Dynamic pivot on two columns in SQL server

Question

5.00/5 (1 vote)

See more:

Hi,

I wanted to make a pivot on two columns in the sql server table which will have to generate dynamic columns. I have tried in the following way

What I have tried:

SQL

CREATE TABLE [dbo].[Test](
	[Writer] [nvarchar](300) NULL,
	[PackageID] [nvarchar](300) NULL,
	[PubType] [nvarchar](300) NULL,
	[RFT] [int] NULL,
)

INSERT INTO Test 
VALUES
('Ajeeth Kumar','723','ABC',1),
('Ajeeth Kumar','724','ABC',1),
('Ajeeth Kumar','725','ABC',1),
('Ajeeth Kumar','726','DEF',1),
('Ajeeth Kumar','727','DEF',0),
('Ajeeth Kumar','728','DEF',0)

SELECT * FROM Test

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(PubType)
            FROM Test 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

PRINT @cols 

SET @query = 'SELECT Writer, ' + @cols + ' from 
              (
                SELECT	Writer						
						,PackageID
						,PubType						
                FROM Test
              )x             
              PIVOT 
              (
                COUNT(PackageID)
                for PubType in (' + @cols + ')
              )p              
              WHERE Writer IS NOT NULL'

    
EXECUTE (@query)

This gives me the out put as i had pivoted only with PubType column

Writer	        ABC	DEF
Ajeeth Kumar	3	3

But i need the output to be pivoted with PubType and RFT column as well so that my output looks like

Writer	        ABC	DEF   0   1
Ajeeth Kumar	3	3     2   4

Any help on this will be appreciated. Thank you!!

Posted 28-Jun-17 1:48am

Ambarish Kesavarapu

Updated 28-Jun-17 20:58pm

Add a Solution

Comments

FranzBe 28-Jun-17 16:58pm

Just a comment: I voted your question up, because you provided the setup (table and data) to quickly play around with the topic asked for. Something I often miss in other questions I read.

2 solutions

Solution 2

There's few ways to achieve that. Please, refer these:
PIVOT on two or more fields in SQL Server – Ken O' Bonn[^]
sql - TSQL PIVOT MULTIPLE COLUMNS - Stack Overflow[^]
SQL Server Pivot Table with multiple column aggregates - Stack Overflow[^]
Pivoting on multiple columns - SQL Server | The SQL Ideas[^]

Posted 28-Jun-17 20:58pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FranzBe · Accepted Answer · 2017-06-28T10:54:00

Have you considered doing something like this:

SELECT
  a.Writer
  ,SUM(a.ABC)  AS ABC
  ,SUM(a.DEF)  AS DEF
  ,SUM(a.RFT_0)  AS RFT_0
  ,SUM(a.RFT_1)  AS RFT_1
FROM
(
SELECT  Writer
  ,CASE WHEN PubType = 'ABC' THEN 1 ELSE 0 END AS ABC
  ,CASE WHEN PubType = 'DEF' THEN 1 ELSE 0 END AS DEF
  ,CASE WHEN RFT = 0 THEN 1 ELSE 0 END AS RFT_0
  ,CASE WHEN RFT = 1 THEN 1 ELSE 0 END AS RFT_1
 FROM TEST
 ) a
GROUP BY
 Writer

It will give you the required output and seems easier to me than the pivot approach. Generating this query dynamic in e.g. c# would not be that hard (although I don't know how to do this in raw sql)