Inserting Blank Lines as Value Changes

Question

Inserting Blank Lines as Value Changes

Colin Betteley

Hall of Fame

Points: 3095
More actions
November 24, 2006 at 1:51 am

#115277

Morning,
I know this can be done in other languages but cannot find the syntax for SQL.
I want to insert a blank line into the result of a select statement when a value changes. I am running a DTS package to export to a spreadsheet and the users want a break as the office of the staff returned changes.
Thanks
Colin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Duncan Sutcliffe Old Hand Points: 312 More actions · Answer 1

You could do this by getting the records for each office one at a time, and inserting a null record between each set of results. I'd do it with a loop. This example assumes you have a table of staff, you want to get a list of staff by office, with a blank line between each office.

-- Get a list of all the offices into a table.

declare @OfficeList table

(Record int identity, OfficeName varchar(250))

insert into @OfficeList (OfficeName)

select distinct OfficeName from Staff

-- How many offices are there?

declare @records int

select @records = max(Record) from @OfficeList

-- We will add the results into a table as we retrieve them

declare @Result table(StaffMember(varchar 250), OfficeName varchar (250))

-- create a counter

declare @record int

set @record = 1

-- loop through each office, getting the staff for each office

while @record <= @records

begin

insert into @Result select StaffMember, OfficeName from Staff where OfficeName = (select OfficeName from @OfficeList where Record = @record)

insert into @Result values (null, null) -- Add the null line

set @record = @record + 1

end

-- Return the data

select

*

from

@result

Obviously this is simplified - your result table would actually contain the correct columns for whatever data you are selecting.

Disadvantage: you are going to be executing the select statement once for each office you have, rather than once to get everything.

Duncan

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

DECLARE @Table TABLE(pKey int,grp int, val int)

INSERT INTO @Table

SELECT 1,1,11 UNION ALL

SELECT 2,1,12 UNION ALL

SELECT 3,1,13 UNION ALL

SELECT 4,2,21 UNION ALL

SELECT 5,2,22 UNION ALL

SELECT 6,3,31 UNION ALL

SELECT 7,3,32 UNION ALL

SELECT 8,3,33

SELECT grp,val FROM @TABLE ORDER BY grp,val

SELECT CASE ord WHEN 0 then ltrim(str(grp)) else '' end grpR,CASE ord WHEN 0 then ltrim(str(val)) else '' end valR FROM

((SELECT grp,val,0 ord FROM

@TABLE ) UNION ALL

(SELECT distinct grp,0,1 ord

FROM @Table))

A

ORDER BY GRP,ORD,val

Kindest Regards,

Vasc

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 3

Thank you for the ideas, much appreciated.

Thank you

Colin

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

I know they asked for blank lines... ask them if this is better...

DECLARE @Table TABLE(pKey int,grp int, val int)

INSERT INTO @Table

SELECT 1,1,11 UNION ALL

SELECT 2,1,12 UNION ALL

SELECT 3,1,13 UNION ALL

SELECT 4,2,21 UNION ALL

SELECT 5,2,22 UNION ALL

SELECT 6,3,31 UNION ALL

SELECT 7,3,32 UNION ALL

SELECT 8,3,33

SELECT CASE WHEN GROUPING(Grp) = 1 THEN 'Grand Total'

WHEN GROUPING(Val) = 1 THEN 'Total'

ELSE Grp

END AS Grp,

CASE WHEN GROUPING(Val) = 1 THEN STR(SUM(Val)) ELSE STR(Val)

END AS Val

FROM (SELECT CAST(Grp AS VARCHAR(10)) AS Grp,Val FROM @Table) d

GROUP BY Grp,Val WITH ROLLUP

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)