SQL Server Pivot multiple values with many rows

Question

Pivots in SQL Server are new to me and have been struggling to come up with the result in the following image. The departments are random. I was looking at a unknown column name pivot query but I can't figure it out. The table will have thousands of rows and a max of 50 departments. Any help would be much appreciated.

This is what I am trying:

select department, employees, pct, up_down
from
(
  select employees, department
  from etl_insight_counts
) d
pivot
(
  max(employees)
  for department in (department, employees, pct, up_down)
) piv;

This is the sample data and result I am trying to achieve.

If the departments are random you will have to use Dynamic SQL to pivot by their names. And since you can't apply 2 or more pivot functions at once, you will have to do 3 pivots (one for employees, one for pct and one for up_down) and join their results by id_request. — EzLo, May 21 '18 at 17:08
I had looked at this dynamic solution; but can't figure it out...https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Rob, May 21 '18 at 17:11
Will there only be one row per `id_request` and `department` combination? — Laughing Vergil, May 21 '18 at 19:41

Alfin E. R. · Answer 1 · 2018-05-26T06:52:33.967

I am using this reference Efficiently convert rows to columns in sql server and some other references, I came up with this query

DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT a.department 
                       FROM_insight_counts a FOR XML PATH(''), TYPE).VALUE('.', 'NVARCHAR(MAX)')

SET @query = N'select *
from
(
  select id_request, department, employees, ca.pct, ca.up_down
  from etl_insight_counts
  CROSS APPLY (VALUES (pct, up_down)) ca (pct, up_down)
) d
pivot
(
  max(employees)
  for department in ('+@cols+')
) piv'

exec sp_executesql @query;

Hope it works as expected

I was missing the (') character at the end, i have edited the query — Alfin E. R., May 26 '18 at 06:53

score 0 · Accepted Answer · answered Jun 05 '18 at 06:03

This is what I eventually came up with, piecing things together. It will dynamically figure out X number of columns to pivot (@cols) and then include them in the pivot result...

DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.dpt) 
                      from etl_insight_counts
                      cross apply 
                      (
                        select department as dpt 
                        union all
                        select department as dpt2
                      ) c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = N'select *
from
(
  select id_request, department, employees, max(ca.up_down) as ud
  from etl_insight_counts 
  Group By id_request, department, employees
) d
pivot
(
  max(employees)
  for department in ('+@cols+')
) piv'

exec sp_executesql @query;

For each value (Example: max(ca.up_down) ) you need a separate pivot query. I put each pivot query into a View. For simplicity I use MS-Access to filter the result by any of the known columns in the result and optionally connect the view columns to other data sources to support quick data analysis. Hope this helps someone in the future.

SQL Server Pivot multiple values with many rows

2 Answers2