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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
ste5anSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.