Pivot Function Help Needed in SQL Server

Question

I am trying to create a pivot table using the following information:

SELECT 

     [RecordId],
     [Source],
     [Channel],
     [Reading],
     [DownloadDateTime]

FROM [dbo].[tblDataSource]

WHERE source = 'CSLF'

turning this…

Source  Channel         Reading  DownloadDateTime
CSLF    Flare_Temp      1305     2016-04-26 22:18:40.290
CSLF    Flare_Flow      671      2016-04-26 22:18:41.600
CSLF    Methane         54.2     2016-04-26 22:18:42.960

Into this…

DownloadDateTime     FlareTemp     FlareFlow    Methane
2016-04-26           1305          671          54.2

Any ideas of how to do this?

Possible duplicate of [SQL server : Convert rows into columns](http://stackoverflow.com/questions/16589282/sql-server-convert-rows-into-columns) — Shakeer Mirza, Jan 31 '17 at 08:06
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Eli, Aug 04 '17 at 15:56

Stephen · Answer 1 · 2017-01-31T08:06:14.897

this should do:

declare @t table (  [RecordId] int,
                     [Source] varchar(max),
                     [Channel] varchar(max),
                     [Reading] dec(18,2),
                     [DownloadDateTime] datetime)

insert @t values (1, 'CSLF', 'Flare_Temp', 1305, '2016-04-26 22:18:40.290'),
                 (2, 'CSLF', 'Flare_Flow', 671, '2016-04-26 22:18:41.600'),
                 (3, 'CSLF', 'Methane', 54.2, '2016-04-26 22:18:42.960')

SELECT DownloadDateTime, Flare_Temp, Flare_Flow, Methane
FROM (
    SELECT [Source], [Channel], [Reading],  CONVERT(CHAR(10), [DownloadDateTime], 121) as DownloadDateTime FROM @t WHERE [Source] = 'CSLF' ) d
     pivot (SUM([Reading]) FOR [Channel] in (Flare_Temp, Flare_Flow, Methane)) piv

Pivot Function Help Needed in SQL Server

1 Answers1