SQLTeam.com | Weblogs | Forums

Transposing with dynamic columns can be done using view? If yes, can you please help on this


#1

Example:

table 1: Signals
ID Date Signal Value
1 5/1/2015 s1 1
2 5/1/2015 s2 2
3 5/1/2015 s3 5
4 5/1/2015 s1 2
2 5/1/2015 s1 3
1 5/1/2015 s3 9

Result:
Id Date s1 s2 s3
1 5/1/2015 1 Null 9
2 5/1/2015 3 2 Null
3 5/1/2015 Null Null 5
4 5/1/2015 2 Null Null

Query that displays result:
Declare @query VARCHAR(Max)
Declare @cols VARCHAR(Max)

Set @cols = STUFF((Select ','+ QUOTENAME([SIGNAL])
From [dbo].[Signals]
group by QUOTENAME([SIGNAL])
Order By QUOTENAME([SIGNAL])
FOR XML PATH(''),TYPE
).value('.', 'NVARCHAR(MAX)') , 1, 1, '')

Set @query= 'select * from
(
select [ID],[DATE],[Value],[SIGNAL]
from [dbo].[Signals]
) as t
PIVOT (MAX(VALUE) FOR [SIGNAL] in (' + @cols + ')) as pvt'

Execute (@query)

Is there any better way to do this?
Can I generate a view/ or a table showing this result?


#2

Short answer: you need a stored procedure.