I have the following table:
oCode oDateTime oValue
---------------------------------------------
A 2017-01-01 10
B 2017-01-01 20
C 2017-01-01 5
I want to have the following result:
oDateTime A B C
------------------------------------------------
2017-01-01 10 20 5
If Static Pivot, I would use the following code:
select
*
from
(
select
sTag
, sDateTime
, sValue
from #condesarsp
) src
pivot
(
sum(sValue)
for sTag in ([X1], [X2], [X3])
) piv
order by sDateTime;
But unluckily, The oValue is not shown. Its just showing null value. Is there a typo on the code above?
After, I want to have dynamic pivot. So I don't need to define the column, It's just generate from oCode value.
Need help, thank you.
Ya there is some mistakes in your query. You have to give the oCode like A,B,C instead of [X1], [X2], [X3]. Like this:
for sTag in (A, B, C)
So the corrected code is:
select
*
from
(
select
oCode
, oDateTime
, oValue
from condesarsp
) src
pivot
(
sum(oValue)
for oCode in (A, B, C) -- This line is changed.
) piv
order by oDateTime;
Follow the link for demo:
Dynamic Query:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.oCode)
FROM condesarsp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT oDateTime, ' + @cols + ' from
(
select oCode
,oDateTime
,oValue
from condesarsp
) x
pivot
(
sum(oValue)
for oCode in (' + @cols + ')
) p '
execute(@query);
Follow the link to the demo: