Click here to Skip to main content
15,306,142 members
Please Sign up or sign in to vote.
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
Updated 28-Jun-17 20:58pm
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.

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)
   
Comments
Maciej Los 29-Jun-17 2:58am
   
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900