SQLTeam.com | Weblogs | Forums

Need help to create a view that pivots the data from a long, narrow table into a wide table


#1

I have a table: 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
........................................
I need a view that pivots the data from above table to a horizontal/ wide table with Signals as column names. The Signal columns needs to be dynamic because the data in the above table gets refreshed on daily basis, and new signals are added.

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
...................................and so on

Please let me know what are your thoughts.


#2

This brings the data, but I don't know how to create a View on this:

Declare @query VARCHAR(Max)
Declare @cols VARCHAR(Max)

Set @cols = STUFF((Select ','+ QUOTENAME([SIGNAL_NAME])
From [dbo].[Signal] with (nolock)
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].[Signal] with (nolock)
) as t
PIVOT (MAX(VALUE) FOR [SIGNAL] in (' + @cols + ')) as pvt'

Execute (@query)