How to PIVOT a select result as mentioned in the query

Question

I have query as mentioned below

SELECT Project
    ,STATUS
    ,count(*) AS [count]
FROM Defect
GROUP BY Project
    ,STATUS
ORDER BY Project

which will return a table like

I'm trying to PIVOT this result as below

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — rinz1er, Jul 12 '20 at 07:02

score 2 · Accepted Answer · answered Jul 12 '20 at 07:06

2

You can try the below way -

SELECT * FROM   
(
    SELECT Project,STATUS,count(*) AS cnt FROM Defect GROUP BY Project,STATUS
) t 
PIVOT(
    sum(cnt) 
    FOR STATUS IN (
        [Build Assigned], 
        [Build Delivered], 
        [Closed], 
        [Eng. Build Delivered], 
        [New], 
        [Non-Issue], 
        [Open],[Pending])
) AS pivot_table

answered Jul 12 '20 at 07:06

Fahmi

37,315
5
22
31

Any way to get 0 instead of NULL if the status not present for a project? – thejustv Jul 12 '20 at 08:57

score 1 · Answer 2 · answered Jul 12 '20 at 07:10

1

would be

with base as
(
SELECT Project
    ,STATUS
    ,count(*) AS [count]
FROM Defect
GROUP BY Project
    ,STATUS
)
select *
from base
pivot (
sum([count])
for base.status in ([Build Delivered],[New],[...],[...])
) as x
order by project
;

answered Jul 12 '20 at 07:10

Michael Hauptmann

354
1
5

score 0 · Answer 3 · answered Jul 12 '20 at 07:07

You can use aggregated function sum with case expressions as following

select
  project,
  sum(case when status = 'Build Assigned' then count else 0 end) as Build_Assigned,
  sum(case when status = 'Build Delivered' then count else 0 end) as Build_Delivered
from yourTable
group by
  project

How to PIVOT a select result as mentioned in the query

3 Answers3