Pivot and grouping SQL Server 2014

Question

I have a table like this

Employee    Response Code
-------------------------
Emp A       vml
Emp B       wrn
Emp C       ptb
Emp A       wrn
Emp B       ulm
Emp A       vml
Emp B       ulm
Emp C       vml

I need to create a report like this from the above table

       vml  wrn ptb ulm
------------------------
Emp A   2   1   0   0
Emp B   0   1   0   2
Emp C   1   0   1   0
Totals  3   2   1   2

The report table calculates the count of each code for each employee and total calculates the sum of each column at the end

How can I do this? Could anyone please help me? Thank you very much for your time and help

I tried grouping . but Did not get the required result. I have actually 100+ response code and 50+ employee in my actual table. I do not know how to pivot and grouping in sql server — Please, Dec 08 '16 at 17:20
@Please - please update your question with the query you've attempted. — devlin carnate, Dec 08 '16 at 17:21
here is your link http://stackoverflow.com/questions/24470/sql-server-examples-of-pivoting-string-data?rq=1 I would note mister blame/shame that this was a simple search on sql server and pivot — Hogan, Dec 08 '16 at 17:27
@Hogan I have an actual table with 50 fields and I need actually 2 column from that table. That is the 2 required column I mentioned in the question . — Please, Dec 08 '16 at 17:28
@Please are values in Response Code column static (predefined) or they can be dynamic, i.e. new possible response codes added over time? — andrews, Dec 08 '16 at 17:31
@andrews Thank you for helping. There is a possibility of adding more response code in future. If I use Case when , I will have to write 100+ case when for each response code. I need it to be dynamic — Please, Dec 08 '16 at 17:34
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — S3S, Dec 08 '16 at 17:40

John Cappelletti · Accepted Answer · 2016-12-14T14:53:42.437

3

I'll bite. Seems like a jump-start is needed.

Below are two options: The first is Dynamic which will allow future response codes. The second will illustrate the actual PIVOT syntax

The Dynamic Option

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName([Response Code]) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = 'Select [Lvl],[Employee],' + @SQL + '
                From (
                        Select [Employee],[Response Code],Cnt=1,Lvl=0 from YourTable
                        Union All
                        Select ''Total'',[Response Code],count(*),1 From YourTable Group By [Response Code]
                     ) A
                Pivot (sum(Cnt) For [Response Code] in (' + @SQL + ') ) p'
Exec(@SQL);

The Non-Dynamic Option

Select [Lvl],[Employee],[ptb],[ulm],[vml],[wrn]
 From  (
        Select [Employee],[Response Code],Cnt=1,Lvl=0 from YourTable
        Union All
        Select 'Total',[Response Code],count(*),1 From YourTable Group By [Response Code]
        ) A
 Pivot (sum(Cnt) For [Response Code] in ([ptb],[ulm],[vml],[wrn]) ) p

Both would return

Employee    ptb     ulm     vml     wrn
Emp A       NULL    NULL    2       1
Emp B       NULL    2       NULL    1
Emp C       1       NULL    1       NULL
Total       1       2       3       2

EDIT - To Remove NULLs

Notice the additional Union All

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName([Response Code]) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = 'Select [Lvl],[Employee],' + @SQL + '
                From (
                        Select [Employee],[Response Code],Cnt=1,Lvl=0 from YourTable
                        Union All
                        Select [Employee],[Response Code],Cnt=0,Lvl=0 from (Select Distinct [Employee] from YourTable) A Join (Select Distinct [Response Code] from YourTable) B on 1=1
                        Union All
                        Select ''Total'',[Response Code],count(*),1 From YourTable Group By [Response Code]
                     ) A
                Pivot (sum(Cnt) For [Response Code] in (' + @SQL + ') ) p'
Exec(@SQL);

edited Dec 14 '16 at 14:53

answered Dec 08 '16 at 18:01

John Cappelletti

79,615
7
44
66

Thank you very much for the help . I will try this solution – Please Dec 08 '16 at 18:05
Good answer, the only problem was with 'Select * ...' and following UNION ALL. I have added PK_Employees column as PK to my test table and UNION ALL complained about column number mismatch. There may be also other columns which SELECT * will fetch and they are not included in the UNION ALL call. So we need the exact CREATE TABLE/INSERT scripts from the OP OR you need to convert * to the list of actual columns in the SELECT statement. – andrews Dec 08 '16 at 18:34
@andrews Took you suggestion and removed the Select * – John Cappelletti Dec 09 '16 at 00:08
@John Cappelletti Thank you very much. Is is working exactly like I wanted – Please Dec 09 '16 at 17:12
@Please Happy to help. – John Cappelletti Dec 09 '16 at 17:16
@Please Don't for get to select it as the answer – John Cappelletti Dec 09 '16 at 17:17
@John Cappelletti Thank you. Unfortunately, I do not have enough points in starkoveflow to mark any answer. Once I got 15 point I will mark the answer. Once again thank you very much for your time and help – Please Dec 09 '16 at 18:39
@Please No worries. I forgot they had the initial threshold. cheers – John Cappelletti Dec 09 '16 at 18:42
@John Cappelletti I tried to replace the null values with empty string. I am not able to get the correct result. I tried to put isnull(count(*),0) and I am getting error message. Could you please help me – Please Dec 14 '16 at 14:20
@Please See EDIT in updaed answer – John Cappelletti Dec 14 '16 at 14:54
@John Cappelletti Million times thank you. It works perfect. This script is very new to me and without your help I could never solve the problem. Please forgive me If I am disturbing you. Is there a way I can export this dynamic result to a table or flat file? Because my goal is to export the result set into excel and send it over. – Please Dec 14 '16 at 15:34
@Please Once again, happy to help – John Cappelletti Dec 14 '16 at 15:35

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

based on your last comment, you need to use Dynamic Pivot, because by default for PIVOT operator you need to provide a static list of columns to pivot.

See my answer to somewhat similar question: How-to-convert-many-to-many-entity-relationship-to-tabular-view-in-sql

Especially pay attention to the dynamic pivot part.

If the mentioned answer is too difficult for you to understand, then search google for SQL Server Dynamic Pivot. Here is a sample result: Script to create dynamic PIVOT queries in SQL Server

Hope this helps. If still in trouble, post in comments what you have tried and what didn't work.

edited May 23 '17 at 11:45

Community

1
1

answered Dec 08 '16 at 17:40

andrews

2,173
2
16
29

Thank you very much for the help. I will try this and let you know. I really appreciate you helping mind. God bless you :) – Please Dec 08 '16 at 17:46
No problem, @Please. You'll learn by try-and-fail. Next time you ask something about SQL be sure to include the CREATE TABLE/INSERT INTO scripts, so that users may replicate your data locally, also try to post what you have tried. This comment may be flagged and removed, but I will say anyways: I'm also a Christian and God has changed my stone-like heart not to be a snob or smth. So may God bless you too and help you learn SQL ;)! – andrews Dec 08 '16 at 18:21
Thank you. I will keep all these things in my mind . – Please Dec 08 '16 at 18:28

Pivot and grouping SQL Server 2014

2 Answers2