CODEWITHSUNDEEP
sqlpivot-table
Orin Moyer
Orin Moyer

Reputation: 509

SQL Pivot report - Not Grouping As Desired

I have a query that returns a 'pivot' result SET. the issue I have is that the parameters I need to match to each timestamp are from slightly different timestamps ---as shown in the image below.

I need to combine each of the 5 parameters ---gty, tmp, rate, mf, prs--- into 1 line under an arbitrary "maxtime" where they ignore the nulls

any ideas??

SELECT [time], Tagname, GTY, TMP, FLOW as Rate, MF, PRS
FROM y
PIVOT
(
       max(lastvalue)
       FOR [tag2] IN (GTY, TMP, FLOW, MF, PRS)
) AS P

SQL Piv

Upvotes: 2

Views: 389

Answers (1)

Michael Z.
Michael Z.

Reputation: 1473

Group it so you get to keep the time portion.
If you need to list all the unique values comma separated instead of max then I have a CLR aggregate function you could use if you are using SQL Server.

SELECT max([time]) AS 'time',
       Tagname,
       max(GTY) AS 'GTY',
       max(TMP) AS 'TMP',
       max(FLOW) AS 'Rate',
       max(MF) AS 'MF',
       max(PRS) AS 'PRS'
FROM y
GROUP BY Tagname

Or wrap your pivot in a group by 

SELECT max([time]) AS 'time',
           Tagname,
           max(GTY) AS 'GTY',
           max(TMP) AS 'TMP',
           max(FLOW) AS 'Rate',
           max(MF) AS 'MF',
           max(PRS) AS 'PRS'
    FROM (SELECT [time], Tagname, GTY, TMP, FLOW as Rate, MF, PRS
          FROM y
          PIVOT
          (
          max(lastvalue)
          FOR [tag2] IN (GTY, TMP, FLOW, MF, PRS)
          ) AS P) y
    GROUP BY Tagname

Or better yet, use CTE 

WITH PIV AS 
(SELECT [time], Tagname, GTY, TMP, FLOW as Rate, MF, PRS
FROM y
PIVOT
(
       max(lastvalue)
       FOR [tag2] IN (GTY, TMP, FLOW, MF, PRS)
) AS P)
SELECT max([time]) AS 'time',
           Tagname,
           max(GTY) AS 'GTY',
           max(TMP) AS 'TMP',
           max(FLOW) AS 'Rate',
           max(MF) AS 'MF',
           max(PRS) AS 'PRS'
    FROM PIV
    GROUP BY Tagname

Upvotes: 1

Related Questions