I Have table having 2 columns
x
Column1 Value
A 10
B 20
C 11
A 40
B 30
C 22
I Want Result like
Column1 Value1 Value2
A 10 40
B 20 30
C 11 22
Is there any query or store Procdure to get such Output
Advertisement
Answer
You require DynamicSql + Pivot operation.I am not aware of any other method.
The below query will give you what you want.
DROP TABLE tmptbl
GO
SELECT * ,
'Value' + (CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (PARTITION BY [Column1] ORDER BY Column1,[Value]))) [ValueColumn]
INTO tmptbl
FROM (
SELECT 'A' [Column1],10 [Value]
UNION ALL
SELECT 'A' [Column1],20 [Value]
UNION ALL
SELECT 'A' [Column1],30 [Value]
UNION ALL
SELECT 'B' [Column1],40 [Value]
UNION ALL
SELECT 'B' [Column1],50 [Value]
UNION ALL
SELECT 'B' [Column1],60 [Value]
UNION ALL
SELECT 'B' [Column1],70 [Value]
) tbl
GO
DECLARE @Vals NVARCHAR(MAX)
SET @Vals = N''
SELECT @Vals = STUFF((SELECT ', ' + QUOTENAME('Value' + CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY [Valuecolumn])))
FROM tmptbl
GROUP BY [Valuecolumn]
FOR XML PATH ('')),1,1,'')
SELECT @vals
DECLARE @strsql NVARCHAR(MAX) = ' SELECT [Column1],' + @vals + '
FROM (
SELECT [Column1],[Value],[ValueColumn]
FROM tmptbl
) t
PIVOT (
AVG([Value])
FOR [ValueColumn] IN (' + @Vals + ')) As p'
EXEC (@strsql)