SQL Pivot Query

Question

I'm trying to do a pivot, however the query I'm using combines several tables to provide the results I need. Here is my query:

Select lc.statename as 'Queue',
       CONVERT(varchar(10), wfl.entrytime, 110) as 'EntryDate',
       count(lc.statenum) as 'Count' -- CONVERT(varchar(10), wfl.entrytime, 110) as 'EntryDate'
FROM lcstate lc
INNER JOIN wflog wfl ON lc.statenum = wfl.statenum 
INNER JOIN lifecycle lif ON wfl.lcnum = lif.lcnum 
INNER JOIN itemdata id ON wfl.itemnum = id.itemnum 

WHERE (lif.lcnum = '163') 
  AND (lc.statenum IN ('836', '837', '848', '863', '845', '859', '860', '864', '865', '866', '816', '867', '869', '868', '870', '872'))
  AND wfl.exittime = '1964-01-01 00:00:00.000'
GROUP BY lc.statename, CONVERT(varchar(10), wfl.entrytime, 110)
Order by 1,2,3 desc

My results are as follows:

Queue          Entry Date            Count
----------------------------------------------------------------
Queue1         01/01/2017             15
Queue1         01/04/2017              9
Queue1         01/21/2017             27
Queue2         01/01/2017              4
Queue2         01/21/2017             12
Queue3         01/01/2017             54

And I'd like to use Column 1 as the column header and the Entry date as the Row header. So it looks something like this:

 EntryDate       Queue1     Queue2     Queue3
-----------------------------------------------
01/01/2017         15          4         54
01/04/2017          9        null       null       
01/21/2017         27         12        null

There are a lot of queues and entry dates involved and I'd like to make the headers dynamic if possible as the queue names change depending on who is running the report. I understand there is a lot of Pivot examples, but most that I've found doesn't quite do what I need. I don't need to do this conversion, however I'm trying to make it as user friendly as possible for the users.

Thanks in advance.

what is your rdbms? You need a dynamic pivot. But each db has a different aproach. — Juan Carlos Oropeza, Mar 10 '17 at 15:07
@McNets You dont need delete, maybe he didnt know how to do it a basic pivot and help him to have something to start. But I think he need a dynamic pivot http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Juan Carlos Oropeza, Mar 10 '17 at 15:15

McNets · Answer 1 · 2017-03-10T15:17:03.357

1

On SQL Server and if there is a well-known number of columns, you can use this syntax.

SELECT Entry, [Queue1], [Queue2], [Queue3]
FROM
(SELECT Entry, Queue, Num 
    FROM @Piv) AS SourceTable
PIVOT
(
SUM(Num)
FOR Queue IN ([Queue1], [Queue2], [Queue3])
) AS PivotTable;

|Entry              |Queue1|Queue2|Queue3|
|:------------------|-----:|-----:|-----:|
|01/01/2017 00:00:00|    15|     4|    54|
|04/01/2017 00:00:00|     9|      |      |
|21/01/2017 00:00:00|    27|    12|      |

dbfiddle here

edited Mar 10 '17 at 15:17

answered Mar 10 '17 at 15:09

McNets

10,352
3
32
61

op says: `I'd like to make the headers dynamic if possible as the queue names change depending on who is running the report` – Juan Carlos Oropeza Mar 10 '17 at 15:11

score 1 · Answer 2 · answered Mar 10 '17 at 15:14

1

In my little head, the easiest way to drop your initial results into a #TempResults table and then run some dynamic SQL.

-- Your Complicated Query into #TempResults

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Queue) From #TempResults  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select *
 From  #TempResults
 Pivot (sum(count) For [Queue] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

Entry Date  Queue1  Queue2  Queue3
2017-01-01  15      4       54
2017-01-04  9       NULL    NULL
2017-01-21  27      12      NULL

answered Mar 10 '17 at 15:14

John Cappelletti

79,615
7
44
66

It's nice not to have to change that unwieldy `where` statement in more than one place as well. – SqlZim Mar 10 '17 at 17:07
@SqlZim My thought exactly ... Sometimes it is OK to be lazy – John Cappelletti Mar 10 '17 at 17:17
Shhh, let's just refer to it as being innovative, efficient, and/or concise. – SqlZim Mar 10 '17 at 17:19

SQL Pivot Query

2 Answers2