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

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)

Dynamic pivot on two columns in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Dynamic pivot on two columns in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us