November 24, 2006 at 1:51 am
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
November 24, 2006 at 6:01 am
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
November 24, 2006 at 12:19 pm
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
Vasc
December 4, 2006 at 3:35 am
Thank you for the ideas, much appreciated.
Thank you
Colin
December 4, 2006 at 11:19 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply