May 16, 2007 at 10:19 am
hi all,
I a bit stuck with this problem. I trying to query the result if col1 have more then 1 and col2 is not the same, retrieve the fields. eg:
table:
col1--col2
1-----a
1-----b
1-----c
1-----c
2-----a
2-----b
2-----b
3-----a
result want:
col1, col2, col3,col4
1,a,b,c
2,a,b
thanks
May 17, 2007 at 6:48 am
what you want is to group by column1 and 2 and count column2 then PIVOT the results
May 17, 2007 at 7:48 am
--You need SQL 2005 for this, but the following accomplishes what you want:
--create an "in-memory" test table
declare @t1_ table (col1_ int, col2_ char(1))
--Populate the test table with your data
insert into @t1_ values (1,'a')
insert into @t1_ values (1,'b')
insert into @t1_ values (1,'c')
insert into @t1_ values (1,'c')
insert into @t1_ values (2,'a')
insert into @t1_ values (2,'b')
insert into @t1_ values (2,'b')
insert into @t1_ values (3,'a')
--Retrieve the data from the test table, with output as desired.
select col1_, p.a as col2Pvt1, p.b as col2Pvt2, p.c as col2Pvt3
from @t1_ t
pivot
(
max(col2_)
for col2_
in (a,b,c)
) as p
where col1_ in (select col1_ from @t1_ group by col1_ having count(*) > 1)
/* OUTPUT:
col1_ col2Pvt1 col2Pvt2 col2Pvt3
----------- -------- -------- --------
1 a b c
2 a b NULL
*/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply