GlenCloncurry GlenCloncurry - 3 months ago 12
SQL Question

Return SQL data from a single column into multiple columns

I have a table with two columns I am interested in.

One column is a date, and the other are tagnames.

I would like to return the tagnames into different columns matched up with the same date.

I can achieve this using subqueries, but is there a more efficient way of doing this? I'm talking about retrieving about 20-30k rows which results in about 300,000 queries

What is the most efficient way of select insert into to ensure that the date lines up with one another.

This is my query at the moment. (I need the value for each tagname to line up in the row)

Thank you

SELECT ah1.DateTime,ah1.Value as TPH,
(select value
from dbo.AnalogHistory
where tagname = 'LS_EM1_NXG.NXG_I'
and datetime = ah1.DateTime
) as EM1_Current,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM2_NXG.NXG_I'
and datetime = ah1.DateTime
) as EM2_Current,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM1_NXG.NXG_SPEED'
and datetime = ah1.DateTime
) as EM1_Speed,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM2_NXG.NXG_SPEED'
and datetime = ah1.DateTime
) as EM2_Speed,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM1_NXG.NXG_P'
and datetime = ah1.DateTime
) as EM1_Power,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM2_NXG.NXG_P'
and datetime = ah1.DateTime
) as EM2_Power,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM1_NXG.NXG_TRQ'
and datetime = ah1.DateTime
) as EM1_Torque,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM2_NXG.NXG_TRQ'
and datetime = ah1.DateTime
) as EM2_Torque,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM1_NXG.NXG_TRQ_UTIL'
and datetime = ah1.DateTime
) as EM1_Torque_U,

(select value
from dbo.AnalogHistory
where tagname = 'LS_EM2_NXG.NXG_TRQ_UTIL'
and datetime = ah1.DateTime
) as EM2_Torque_U,

(select value
from dbo.AnalogHistory
where tagname = 'LS_TE754001G.PVAI'
and datetime = ah1.DateTime
) as EM1_NDE,

(select value
from dbo.AnalogHistory
where tagname = 'LS_TE754001H.PVAI'
and datetime = ah1.DateTime
) as EM1_DE

FROM [Runtime].[dbo].[AnalogHistory] ah1
where TagName = 'CR_WQI752010.PVAI'
and wwResolution = '600000'
and DateTime > '20160816'
and wwRetrievalMode = 'cyclic'

Answer

Perhaps you can use conditional aggregation. Something like this:

select datetime,
       max(case when tagname = 'LS_EM1_NXG.NXG_I' then value end) as val1,
       . . .
from dbo.AnalogHistory
group by datetime ;