June 12, 2012 at 3:09 pm
I have a table with an id and code(char(3))
100, XXX
100, yyy
100, zzz
101, abc
101, XXX
I'm trying to get the result
100, "XXX,yyy,zzz"
101, "abc,xxx"
I tried PIVOT without success. Any Ides?
Thanks,
Walt
June 12, 2012 at 3:16 pm
I can't test it, but something like this should work.
SELECT outd.id ,
List =
STUFF((SELECT ','+ ind.code
FROM YourTable ind
WHERE ind.id = outd.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
, 1,1,'')
FROM YourTable outd
GROUP BY outd.id
June 12, 2012 at 4:12 pm
Thanks for the help, works perfectly.
Walt
June 14, 2012 at 3:20 am
Other than Roryp's Solution, there are a no. of ways(including Pivot) you can do this. Here are a few:
--Creating Table
Create Table Ex
(Id int,
Value Char(3) )
--Inserting Sample Data
Insert into Ex
Select 100, 'XXX'
Union ALL
Select 100, 'yyy'
Union ALL
Select 100, 'zzz'
Union ALL
Select 101, 'abc'
Union ALL
Select 101, 'XXX'
--Query Using Case
Select ID,
Max(Case When rn = 1 Then value Else '' End) As Value1,
Max(Case When rn = 2 Then value Else '' End) As Value2,
Max(Case When rn = 3 Then value Else '' End) As Value3
From
(Select *, ROW_NUMBER() Over (Partition By Id Order By (Select NULL) ) As rn From Ex) As a
Group By Id
--Static Pivot
Select Id, IsNULL([Value1], '') As Value1, IsNULL([Value2], '') As Value2, IsNULL([Value3], '') As Value3 From
(Select *, 'Value' + Cast(ROW_NUMBER() Over (Partition By Id Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
Pivot
(Max(Value) For rn IN ([Value1], [Value2], [Value3]) ) As Pvt
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(10) )
Insert Into @temp
Select Distinct rn From
(Select *, 'Value' + Cast(ROW_NUMBER() Over (Partition By Id Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp
Set @sql = 'Select Id, '+@cols+' From
(Select *, ''Value'' + Cast(ROW_NUMBER() Over (Partition By Id Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
Pivot
(Max(Value) For rn IN ('+@cols+') ) As Pvt'
Execute (@sql)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply