Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to achieve this result?

Author  Topic 

gautham.gn
Starting Member

19 Posts

Posted - 2014-10-13 : 10:42:29
I have come across this and I felt a little difficulty in doing this. please go through this once.

create table example
( a int null,
b varchar(100) null,
c varchar(100) null,
d int null)

insert into example
select 220,'abc','yes',1 union all
select 220,'abc','yes',2 union all
select 220,'abc','yes',3 union all
select 220,'abc','yes',4 union all
select 220,'abc','no',132 union all
select 220,'abc','no',23 union all
select 220,'abc','no',34 union all
select 220,'abc','no',43 union all
select 243,'raju','NA',123 union all
select 243,'raju','NA',456 union all
select 243,'raju','NA',789 union all
select 243,'raju','Reg',21 union all
select 243,'raju','Reg',23 union all
select 243,'raju','Reg',12

I want to display the result as

220,'abc', 'yes(1,2,3,4),no(132,23,34,43)'
243,'raju', 'NA(123,456,789),Reg(21,23,12)'

Can u please help me with this?

Thanks
Gautham

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 12:38:13
Here's one way:


;
WITH x( a ,
b ,
x )
AS (
SELECT e1.a ,
e1.b ,
e1.c + '(' + STUFF( MAX( a.x ) , 1 , 1 , '' ) + ')' x
FROM
@example e1

CROSS APPLY(
SELECT ',' + CAST( e2.d AS varchar( 5 ))
FROM @example e2
WHERE e1.a = e2.a
AND e1.b = e2.b
AND e1.c = e2.c
--group by e2.a
FOR XML PATH( '' ))a( x )
GROUP BY e1.a ,
e1.b ,
e1.c
)

SELECT CAST( a AS varchar( 5 )) + ',''' + b + ''',' + STUFF(
(
SELECT ',' + x
FROM x y
WHERE x.a = y.a
AND x.b = y.b
FOR XML PATH( '' )
) , 1 , 1 , '' )
FROM x
GROUP BY a ,
b;
Go to Top of Page
   

- Advertisement -