Groupby using PIVOT

Question

I have this table:

Person  Job
PersonA XX
PersonA XX
PersonA XX
PersonB XX
PersonB XX
PersonB YY
PersonB ZZ
PersonC XX
PersonC XX
PersonA XX
PersonA YY
PersonB ZZ
...

Now, I want the output to be something like this:

Job PersonA PersonB Person C
XX  4       2       2
YY  1       1       0
ZZ  0       2       0

So far I have this:

SELECT DISTINCT Person,
                       (SELECT COUNT(Job)
                        FROM dbo.TableName
                        GROUP BY Job)
FROM dbo.ExcelImport

No luck :(

Could you not address this in your `table` or normalizing data? — Jakub, Aug 23 '13 at 15:32
Try looking at the pivot function, some links: http://stackoverflow.com/questions/13031846/manipulate-results-to-display-rows-as-columns http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query — JsonStatham, Aug 23 '13 at 15:32

score 3 · Accepted Answer · edited Aug 23 '13 at 15:39

3

You shoud try something like this:

select
    Job
    ,SUM(case when Person = 'PersonA'
        then
            1
        else
            0
    end) as 'PersonA'
    ,SUM(case when Person = 'PersonB'
        then
            1
        else
            0
    end) as 'PersonB'
    ,SUM(case when Person = 'PersonC'
        then
            1
        else
            0
    end) as 'PersonC'
from
    TableName
group by
    Job

edited Aug 23 '13 at 15:39

Johann Blais

9,389
6
45
65

answered Aug 23 '13 at 15:36

Justas

132
4

Awesome! Thats what I was looking for :) – RG-3 Aug 23 '13 at 15:39

score 2 · Answer 2 · answered Aug 23 '13 at 15:40

2

It is easy task for pivot operator:

select *
from (select Job as Job2, * from [TableName]) t
    pivot (count(Job2) for Person in ([PersonA],[PersonB],[PersonC])) p

answered Aug 23 '13 at 15:40

i-one

5,050
1
28
40

score 0 · Answer 3 · answered Aug 23 '13 at 15:44

0

you can use pivot too

select * from per
pivot (count(job)  for job in ([xx],[yy],[zz]))

answered Aug 23 '13 at 15:44

Dhaval

2,801
20
39

score 0 · Answer 4 · answered Aug 24 '13 at 06:24

Try this if persons are is fixed

SELECT * FROM Table1
PIVOT
(
    COUNT([Person]) FOR [Person] IN ([PersonA],[PersonB],[PersonC])
) p

SQL FIDDLE DEMO

If persons vary then try this dynamic query

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME([Person]) 
                    from Table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT [Job],' + @cols + ' 
             FROM
             (
                Select *
                FROM Table1
             ) T
             PIVOT
             (
                COUNT([Person])
                FOR [Person] IN (' + @cols + ')
             ) pvt '

EXECUTE(@query);

Groupby using PIVOT

4 Answers4