MiguelH MiguelH - 7 months ago 8
SQL Question

SQL - advice on grouping

SQL Server 2005. I am not after a coded answer here (although it would be nice). I'm really after advice on the best way forward to get the result I need. I have some knowledge of pivot/unpivot/cte//rownumber and dynamic queries but cannot get my head around this particular problem! An example of the data follows. Note: The occurrence of type,location,name and description can be none to many.

drop table #temp
create table #temp
(
event int,
type varchar(20),
locations varchar(20),
name varchar(30),
description varchar(50)
)
insert into #temp values (1,'support','r1','fred','desc 1')
insert into #temp values (1,'support','r1','fred','desc 2')
insert into #temp values (1,'support','r1','fred','desc 3')

insert into #temp values (1,'support','r1','jim','desc 1')
insert into #temp values (1,'support','r1','jim','desc 2')
insert into #temp values (1,'support','r1','jim','desc 3')

insert into #temp values (1,'support','r2','fred','desc 1')
insert into #temp values (1,'support','r2','fred','desc 2')
insert into #temp values (1,'support','r2','fred','desc 3')

insert into #temp values (1,'support','r2','jim','desc 1')
insert into #temp values (1,'support','r2','jim','desc 2')
insert into #temp values (1,'support','r2','jim','desc 3')

insert into #temp values (1,'work','r1','fred','desc 1')
insert into #temp values (1,'work','r1','fred','desc 2')
insert into #temp values (1,'work','r1','fred','desc 3')

insert into #temp values (1,'work','r1','jim','desc 1')
insert into #temp values (1,'work','r1','jim','desc 2')
insert into #temp values (1,'work','r1','jim','desc 3')

insert into #temp values (1,'work','r2','fred','desc 1')
insert into #temp values (1,'work','r2','fred','desc 2')
insert into #temp values (1,'work','r2','fred','desc 3')

insert into #temp values (1,'work','r2','jim','desc 1')
insert into #temp values (1,'work','r2','jim','desc 2')
insert into #temp values (1,'work','r2','jim','desc 3')

select * from #temp


The result I am after is this ..

1,support;work,r1;r2,fred;jim,desc1;desc2;desc3


Progress so far...(thanks to @NayruLove)

with cte1 as
(
SELECT
SS.event,
stuff((SELECT distinct'; ' + US.type
FROM #temp US
WHERE US.event = SS.event
FOR XML PATH('')),1,1,'')types--[SECTORS/USERS]
FROM #temp SS
GROUP BY SS.event,ss.type
),
cte2 as
(
SELECT
SS.event,
stuff((SELECT distinct'; ' + US.locations
FROM #temp US
WHERE US.event = SS.event
FOR XML PATH('')),1,1,'') locations
FROM #temp SS
GROUP BY SS.event,ss.locations
),
cte3 as
(
SELECT
SS.event,
stuff((SELECT distinct'; ' + US.name
FROM #temp US
WHERE US.event = SS.event
FOR XML PATH('')),1,1,'') name--[SECTORS/USERS]
FROM #temp SS
GROUP BY SS.event,ss.name
),
cte4 as
(
SELECT
SS.event,
stuff((SELECT distinct'; ' + US.description
FROM #temp US
WHERE US.event = SS.event
FOR XML PATH('')),1,1,'') description--[SECTORS/USERS]
FROM #temp SS
GROUP BY SS.event,ss.description
)
select distinct cte1.event,cte1.types,cte2.locations,cte3.name,cte4.description
from
cte1 inner join cte2 on cte1.event = cte2.event
inner join cte3 on cte1.event = cte3.event
inner join cte4 on cte1.event = cte4.event


result

event types locations name description
1 support; work r1; r2 fred; jim desc 1; desc 2; desc 3


.. but is this the most efficient method of getting this result as I need to apply this to 25,000 + rows?

Answer

Your goal seem to select all distinct value of all columns, then Concatenate into one string. And you only need advice, so I recommend you go here: multiple rows into a single row

It seem that you need more help:

select distinct
stuff((SELECT distinct'; ' + type-- as type
        FROM #temp 
        --order by type
        FOR XML PATH('')),1,1,'')
+ (SELECT distinct'; ' + locations
        FROM #temp 
        FOR XML PATH(''))
+ (SELECT distinct'; ' + name
        FROM #temp 
        FOR XML PATH(''))
+ (SELECT distinct'; ' + description 
        FROM #temp 
        FOR XML PATH(''))
 from #temp;

If you need 4 columns, then change + (SELECT to , stuff((SELECT

The query is just that simple: get distinct of one column, change into string, then concatenate + string of (next column)...