PIVOT for in many column headers using field

Question

I have the following basic SQL statement which uses the PIVOT command.

SELECT *
FROM

(
--select statement that creates my dataset 
) s

PIVOT (Max(incidentcount) for dept in ([dept1],[dept2])) p

This does what I expect it to do, it gives me a count of incidents per reason with depts as my columns. My problem is the departments that I am using for my columns go from 1-60. Is there anyway I can tell the query to use the column Department to populate the PIVOT in part. Obviously I want to avoid manually typing each department.

EDIT

This is the sql that creates my dataset that I use in the pivot...

SELECT Details, Department , count(*) NoIncidents
  FROM myincidentdb
  Group by Details,  Department

EDIT 2

This is what my sql query shows before I attempt to PIVOT it

Taryn · Accepted Answer · 2012-11-09T15:31:32.027

5

You will want to use dynamic sql to PIVOT this, your code will be similar to this:

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

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(dept) 
                    from yourtablewithDepartments
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
              from 
              (
                <your query goes here>
              ) src
              pivot 
              (
                max(incidentcount)
                for dept in (' + @cols + ')
              ) piv '

execute(@query)

If you post more details like table structure, etc then this can be refined to meet your needs.

Edit, based on your current query, it looks like you can use the following:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(department) 
                    from myincidentdb
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT  Details, ' + @cols + ' 
              from 
              (
                select Details, Department
                from myincidentdb 
              ) x
              pivot 
              (
                count(Department)
                for Department in (' + @cols + ')
              ) p '

execute(@query)

See SQL Fiddle with Demo

edited Nov 09 '12 at 15:31

answered Nov 09 '12 at 15:12

Taryn

242,637
56
362
405

have added the sql statement that creates my dataset. – JsonStatham Nov 09 '12 at 15:17
can you post some sample data? or even create a sqlfiddle. – Taryn Nov 09 '12 at 15:18
I have added an example result set when I run my SQL, only 6 departments showing in the example but there are many more, same with Detail. – JsonStatham Nov 09 '12 at 15:25
@SelectDistinct please see my edit, I also made a slight change so then you don't need to use two aggregate functions. – Taryn Nov 09 '12 at 15:26
what is the colList doing? Is this something I should be replacing with count(*) or leave it as colList? – JsonStatham Nov 09 '12 at 15:31
4

Your second query doesn't really need to select `Department` twice. It would be all right to reference the same column twice in `pivot`, i.e. `count(Department) for Department in (...)`. (I think I've done that in my queries without a problem.) – Andriy M Nov 09 '12 at 15:31
@AndriyM well look at that, I learned something new. Thanks for that – Taryn Nov 09 '12 at 15:32
@SelectDistinct please see my edit, it should be fixed now thanks to AndriyM. You don't need the inner `count(*)` because the `count()` in the `PIVOT` will take care of the aggregation. – Taryn Nov 09 '12 at 15:33

PIVOT for in many column headers using field

1 Answers1