MS SQL Server Pivot on two columns

I need to do a double pvit with counts

Select Origin, IndividualID, Cycle, StateCode from Individuals
There are 6 cycles and 56 "states"

The count will be on IndividualID with a PIVOT on the Cycle and the state codes

An example output would be...

Origin      Cycle1     Cycle2     Cycle3     Cycle3     Cycle4     Cycle5     Cycle6     AL     AZ     etc...
MyOrigin    3              30            19             21             3               5              6         22       54    etc....
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
From the relational database viewpoint this is an invariant table.

Then, much worse: You allow SQL injection. E.g.

USE tempdb;
GO

CREATE PROCEDURE dbo.InventoryReport3
    @DateRangeStart NVARCHAR(MAX) ,
    @DateRangeEnd NVARCHAR(MAX)
AS
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM sys.objects';
    IF @DateRangeStart <> ''
        SET @Sql = @Sql + ' WHERE DATEDIFF(DAY,''' + @DateRangeStart + ''',create_date) >= 0 AND DATEDIFF(DAY,create_date,''' + @DateRangeEnd + ''') >= 0 ';

    PRINT @Sql;
    EXEC ( @Sql );
GO

DECLARE @Start NVARCHAR(MAX) = FORMAT(GETDATE(), 'yyyyMMdd');

DECLARE @Normal NVARCHAR(MAX) = N'20181231';
EXECUTE dbo.InventoryReport3 @Start ,
                             @Normal;

DECLARE @Inject NVARCHAR(MAX) = N'20181231' + ''') >= 0 OR 1=1 --';
EXECUTE dbo.InventoryReport3 @Start ,
                             @Inject;
GO

DROP PROCEDURE dbo.InventoryReport3;
GO

Open in new window


This should be changed..

To use dynamic SQL to generate your code you need persistent table. But the structure is the same as

DECLARE @Individuals TABLE
    (
        Origin VARCHAR(255) ,
        IndividualID INT ,
        Cycle INT ,
        StateCode VARCHAR(3)
    );

INSERT INTO @Individuals ( Origin ,
                           IndividualID ,
                           Cycle ,
                           StateCode )
VALUES ( 'MyOrigin', 1, 1, 'AL' ) ,
       ( 'MyOrigin', 2, 2, 'AL' ) ,
       ( 'MyOrigin', 3, 1, 'AZ' ) ,
       ( 'MyOrigin', 4, 2, 'AZ' ) ,
       ( 'MyOrigin', 5, 2, 'AZ' ) ,
       ( 'MyOrigin', 6, 2, 'A''R' );

DECLARE @CycleColumns NVARCHAR(MAX);
DECLARE @StateColumns NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX) = N'
SELECT   I.Origin ,
         @CycleColumns ,
         @StateColumns
FROM     @Individuals I
GROUP BY I.Origin;
';

WITH Cycles
AS ( SELECT DISTINCT I.Cycle
     FROM   @Individuals I )
SELECT @CycleColumns = STUFF((   SELECT REPLACE(', SUM(CASE WHEN I.Cycle = ? THEN 1 ELSE 0 END) AS ' + QUOTENAME('C?'), '?', CAST(C.Cycle AS NVARCHAR(MAX)))
                                 FROM   Cycles C
                                 FOR XML PATH('')) ,
                             1 ,
                             2 ,
                             '');

WITH States
AS ( SELECT DISTINCT I.StateCode
     FROM   @Individuals I )
SELECT @StateColumns = STUFF(
                       (   SELECT REPLACE(', SUM(CASE WHEN I.StateCode = ''?'' THEN 1 ELSE 0 END) AS ', '?', REPLACE(S.StateCode, '''', ''''''))
                                  + QUOTENAME(S.StateCode)
                           FROM   States S
                           FOR XML PATH('')) ,
                       1 ,
                       2 ,
                       '');

SET @Sql = REPLACE(@Sql, '@CycleColumns', @CycleColumns);
SET @Sql = REPLACE(@Sql, '@StateColumns', @StateColumns);

PRINT @Sql;

/* Generated result
SELECT   I.Origin ,
         SUM(CASE WHEN I.Cycle = 1 THEN 1 ELSE 0 END) AS [C1], SUM(CASE WHEN I.Cycle = 2 THEN 1 ELSE 0 END) AS [C2] ,
         SUM(CASE WHEN I.StateCode = 'AL' THEN 1 ELSE 0 END) AS [AL], SUM(CASE WHEN I.StateCode = 'A''R' THEN 1 ELSE 0 END) AS [A'R], SUM(CASE WHEN I.StateCode = 'AZ' THEN 1 ELSE 0 END) AS [AZ]
FROM     @Individuals I
GROUP BY I.Origin;
*/

Open in new window

0
 
ste5anSenior DeveloperCommented:
It's a classic GROUP BY with a conditional SUM:

DECLARE @Individuals TABLE
    (
        Origin VARCHAR(255) ,
        IndividualID INT ,
        Cycle INT ,
        StateCode CHAR(2)
    );

INSERT INTO @Individuals ( Origin ,
                           IndividualID ,
                           Cycle ,
                           StateCode )
VALUES ( 'MyOrigin', 1, 1, 'AL' ) ,
       ( 'MyOrigin', 2, 2, 'AL' ) ,
       ( 'MyOrigin', 3, 1, 'AZ' ) ,
       ( 'MyOrigin', 4, 2, 'AZ' ) ,
       ( 'MyOrigin', 5, 2, 'AZ' ) ,
       ( 'MyOrigin', 6, 2, 'AR' );

SELECT   I.Origin ,
         SUM(CASE WHEN I.Cycle = 1 THEN 1
                  ELSE 0
             END) AS C1 ,
         SUM(CASE WHEN I.Cycle = 2 THEN 1
                  ELSE 0
             END) AS C2 ,
         SUM(CASE WHEN I.StateCode = 'AL' THEN 1
                  ELSE 0
             END) AS AL ,
         SUM(CASE WHEN I.StateCode = 'AZ' THEN 1
                  ELSE 0
             END) AS AL ,
         SUM(CASE WHEN I.StateCode = 'AR' THEN 1
                  ELSE 0
             END) AS AR
FROM     @Individuals I
GROUP BY I.Origin;

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Stephan,
 I would rather not have 50 or 60 SUM's for the states.

Is there not a way to do this with a pivot on two columns?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
ste5anSenior DeveloperCommented:
While it sounds counterintuitive even in a PIVOT you need to code it and as it is a static list is it faster (query plan and execution) to do so. Just use Excel or dynamic SQL to generate the SUM() parts.
0
 
Larry Bristersr. DeveloperAuthor Commented:
ste5an

This IS in dynamic SQL in fact

From our web page we pass in paramaters one of which the end user gets to select 0-all states
That is passed in as a comma seperated string...

I have the entire SQL Set below
I am trying to figure out HOW to get the Cycle in 6 columns.  It will always be a value of 1-1

ALTER PROCEDURE [dbo].[InventoryReport3]
    @DateRangeStart VARCHAR(MAX) ,
    @DateRangeEnd VARCHAR(MAX) ,
    @Income VARCHAR(MAX) ,
    @OriginSalesSiteID VARCHAR(MAX) ,
    @Cycle VARCHAR(MAX) ,
    @VenueID VARCHAR(MAX) ,
    @VendorCode VARCHAR(MAX) ,
    @IndividualCategoryID VARCHAR(MAX) ,
    @BasicCount BIT = 1 ,
    @ByState BIT = 0 ,
    @NQCycle VARCHAR(MAX) = '6' ,
    @CheckForAddressDupes BIT = 0 ,
    @IsNotOnCampaign BIT = 1 ,
    @StateID VARCHAR(MAX) ,
    @ProgramID VARCHAR(MAX) = ''
AS
    DECLARE @SQL VARCHAR(4000);

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

    IF @VendorCode <> ''
        BEGIN
            DECLARE @VendorID VARCHAR(100);
            SET @VendorID = (   SELECT VendorID
                                FROM   dbo.Vendors
                                WHERE  VendorCode = @VendorCode );
        END;

    SET @SQL = '
SELECT * FROM ( SELECT a.IndividualID,c.statecode, b.OriginID,
					c.OriginName as OriginName,
				c.OriginID as OriginCode,
				c.City as OriginCity,
			c.StateCode as OriginState,
				c.SiteName as SiteName,
				c.SiteCode as SiteCode,	
					c.Comments as Comments,
					c.DateStart as DateStart,
					c.ProgramName,c.ProgramID from Individuals a join   
          IndividualOrigins b on a.IndividualID = b.IndividualID join

              (
					select	a.OriginID,
							a.OriginName,
							a.OriginCode,
							a.City,
							b.StateCode,
							a.DateStart,
							isNull(c.[Name],'''''''') as SiteName,
							isNull(c.Code,'''''''') as SiteCode,
							isNull(a.Comments,'''''''') as Comments,
							a.VendorID,a.SalesSiteID,a.VenueID,P.ProgramID,P.Name as ProgramName 
					from	dbo.Origins a with(nolock) join 
							dbo.States b with(nolock) on a.StateID = b.StateID join
							dbo.SalesSites c with(nolock) on a.SalesSiteID = c.SalesSiteID
							LEFT JOIN dbo.Programs p with(nolock) ON a.ProgramID = p.ProgramID and P.Active=1
							
							
							 ) c on b.OriginID = c.OriginID ';

    SET @SQL = @SQL + '
          where a.dnc = 0 and 
				a.cycle < 6 and 
				a.dupe = 0 ';

    IF @IsNotOnCampaign = 1
        BEGIN
            SET @SQL = @SQL
                       + ' and a.IndividualID 		
				not in
				(
				Select a.IndividualID
				from 
				(Select max(IndividualExportsMasterID) as CampaignID,IndividualID
				from IndividualExportsDetail with(nolock)
				group by IndividualID) a 
				join
				IndividualExportsDetail b with(nolock) on a.CampaignID  = b.IndividualExportsMasterID and a.IndividualID = b.IndividualID
				and datediff(day,b.DateExpiration,getdate()) < 0
				) ';

        END;

    IF @DateRangeStart <> ''
        BEGIN
            SET @SQL = @SQL + ' and datediff(day,''' + @DateRangeStart + ''',a.DateAdded) >= 0 and 
						   datediff(day,a.DateAdded,''' + @DateRangeEnd + ''') >= 0 ';
        END;


    IF @OriginSalesSiteID <> ''
        BEGIN
            SET @SQL = @SQL + 'and c.SalesSiteID in (select * from dbo.ParseList(''' + @OriginSalesSiteID + ''','','')) ';
        END;

    IF @Income <> ''
        BEGIN
            SET @SQL = @SQL + 'and a.IndividualID in (Select IndividualID from dbo.IndividualDemographics where DemographicValueID in (select * from dbo.ParseList(''''' + @Income + ''''','''',''''))) ';
        END;

    IF @Cycle <> ''
        BEGIN
            SET @SQL = @SQL + 'and a.Cycle in (select * from dbo.ParseList(''' + @Cycle + ''','','')) ';
        END;

    IF @VenueID <> ''
        BEGIN
            SET @SQL = @SQL + 'and b.OriginID in (Select OriginID from Origins where VenueID in (select * from dbo.ParseList(''' + @VenueID + ''','',''))) ';
        END;

    IF @StateID <> ''
        BEGIN
            SET @SQL = @SQL + 'and a.stateid in (select * from dbo.ParseList(''' + @StateID + ''','','')) ';
        END;

    IF @ProgramID <> ''
        BEGIN
            SET @SQL = @SQL + ' and b.OriginID in (select OriginID from dbo.Origins where ProgramID =' + @ProgramID + ') ';
        END;

    IF @IndividualCategoryID <> ''
        BEGIN
            SET @SQL = @SQL + 'and a.IndividualCategoryID  = ''' + @IndividualCategoryID + ''' ';
        END;

    DECLARE @SQLStates VARCHAR(MAX);
    IF @StateID = ''
        BEGIN
            SELECT @SQLStates = STUFF((   SELECT   '],[' + LTRIM(StateCode)
                                          FROM     States
                                          WHERE    ActiveLeadEntry = 1
                                                   AND CountryCode = 'US'
                                          ORDER BY StateID --'],[' + ltrim(stateid) 
                                          FOR XML PATH('')) ,
                                      1 ,
                                      2 ,
                                      '') + ']';
        END;

    IF @StateID <> ''
        BEGIN
            SELECT @SQLStates = STUFF((   SELECT   '],[' + LTRIM(StateCode)
                                          FROM     States
                                          WHERE    ActiveLeadEntry = 1
                                                   AND CountryID = 224
                                                   AND StateID IN (   SELECT *
                                                                      FROM   dbo.ParseList('' + @StateID + '', ',') )
                                          ORDER BY StateID --'],[' + ltrim(stateid) 
                                          FOR XML PATH('')) ,
                                      1 ,
                                      2 ,
                                      '') + ']';

        END;

    SET @SQL = @SQL + ') o PIVOT (COUNT(IndividualID) FOR StateCode  IN (' + @SQLStates + ')) p 
	ORDER BY OriginName';
    PRINT ( @SQL );
    EXEC ( @SQL );

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.