Query separate value from single column

Question

Click Show Picture

SELECT 
    [Date_Time],
    [TagID],
    [Value_Per_15min]
FROM 
    [RIDTL].[dbo].[Rawdata]
WHERE 
    Date_Time BETWEEN '01-09-2016 00:00:00:000' AND '2016-10-12 23:59:59:000'  
ORDER BY 
    Date_Time

I want output :

Output Picture

Why do you only want the top 2 tags instead of all 4? and in what order? — Cine, Oct 13 '16 at 04:34
because order DATE TIme 00:00:00 to 23:45:00 by each datetime have different TagID — Suradet Leelakiatwanich, Oct 13 '16 at 04:37
Possible Duplicate: http://stackoverflow.com/questions/14590859/build-sql-query-with-dynamic-columns — Cine, Oct 13 '16 at 04:37
See this, you will need dynamic pivot to do this - http://stackoverflow.com/questions/21325394/dynamic-pivot-table-with-multiple-columns-in-sql-server — AT-2017, Oct 13 '16 at 05:04

score 0 · Answer 1 · answered Oct 13 '16 at 05:51

Assuming you wanted to pick the first two values of TagID with respective to to the columns Date_Time and Value_Per_15min.

SELECT [Date_Time],[1] TagID1,[2] TagID2,[Value_Per_15min]
FROM
(SELECT 
    [Date_Time],
    [TagID],
    [Value_Per_15min],
    ROW_NUMBER()OVER(PARTITION BY [Date_Time],[Value_Per_15min] ORDER BY [TagID]) CNT
FROM [RIDTL].[dbo].[Rawdata]
WHERE  Date_Time BETWEEN '01-09-2016 00:00:00:000' AND '2016-10-12 23:59:59:000')t
PIVOT
(MIN(TagID)
 FOR CNT IN ([1],[2]))piv;

Query separate value from single column

1 Answers1