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?