CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivotpivot-table
Miles Freeborn
Miles Freeborn

Reputation: 9

Pivot Function Help Needed in SQL Server

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?

Upvotes: 0

Views: 39

Answers (1)

Stephen
Stephen

Reputation: 1542

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

Upvotes: 2

Related Questions