CODEWITHSUNDEEP
sql-servert-sql
Ryan Taite
Ryan Taite

Reputation: 839

Dynamic Pivot Table, Rows not Condensing Correctly

Using a Dynamic Pivot Table, I'm trying to display rows for each Year and Month (YM), their Zip code, Region, the count of New Callers, the count of Repeat Callers, and a count of what type of Callers these are.

In a scenario where I have the same YM, Zip, Region but both New and Repeat Callers I expect to see a row for both New and Repeat so I can differentiate between the Caller Type counts.

Here is my SQL Fiddle: http://sqlfiddle.com/#!18/56bc6/1

I have setup the data prior to being pivoted as [InitialData] and the expected results as [ExpectedResults]

The third result set shown in that link is where I'm at now.

Here I have hidden the Create and Insert SQL scripts

CREATE TABLE InitialData
	(
	   [YM] int -- YYYYMM date format
	  ,[Zip] varchar(5)
	  ,[Region] varchar(18)
	  ,[NewCallers] int
	  ,[RepeatCallers] int
	  ,[CallerType] varchar(27)
	  ,[CallerTypeCount] int
	)
;
	
INSERT INTO InitialData
	([YM], [Zip], [Region], [NewCallers], [RepeatCallers], [CallerType], [CallerTypeCount])
VALUES
	(201805, NULL, NULL, 3, 0, 'Family / Friend', 3),
	(201805, NULL, NULL, 2, 0, 'Other', 2),
	(201805, NULL, NULL, 86, 0, 'Parent', 86),
	(201805, NULL, NULL, 6, 0, 'Professional', 6),
	(201805, '03598', NULL, 1, 0, 'Parent', 1),
	(201805, '56401', NULL, 1, 0, 'Parent', 1),
	(201805, '72209', NULL, 1, 0, 'Parent', 1),
	(201805, '85007', 'Phoenix South', 1, 0, 'Parent', 1),
	(201805, '85008', 'Phoenix South', 0, 3, 'Other', 3),
	(201805, '85008', 'Phoenix South', 2, 0, 'Family / Friend', 2),
	(201805, '85008', 'Phoenix South', 4, 0, 'Parent', 4),
	(201805, '85008', 'Phoenix South', 2, 0, 'Professional', 2),
	(201805, '85008', 'Phoenix South', 1, 0, 'Business', 1),
	(201805, '85009', 'Phoenix South', 1, 0, 'Parent', 1),
	(201805, '85013', 'Phoenix North', 2, 0, 'Parent', 2),
	(201805, '85014', 'Phoenix North', 1, 0, 'Parent', 1),
	(201805, '85143', 'Pinal', 2, 0, 'Parent', 2),
	(201805, '85201', 'Southeast Maricopa', 0, 4, 'Other', 4),
	(201805, '85203', 'Southeast Maricopa', 1, 0, 'Parent', 1),
	(201806, NULL, NULL, 1, 0, 'Other', 1),
	(201806, NULL, NULL, 70, 0, 'Parent', 70),
	(201806, NULL, NULL, 9, 0, 'Professional', 9),
	(201806, '85257', 'East Maricopa', 1, 0, 'Parent', 1),
	(201806, '85258', 'East Maricopa', 0, 2, 'Other', 2),
	(201806, '85258', 'East Maricopa', 2, 0, 'Parent', 2),
	(201806, '85283', 'East Maricopa', 6, 0, 'Parent', 6)
;

CREATE TABLE ExpectedResults
(
   [YM] int
  ,[Zip] varchar(5)
  ,[Region] varchar(18)
  ,[NewCallers] int
  ,[RepeatCallers] int
  ,[Business] int
  ,[Family / Friend] int  
  ,[Other] int
  ,[Parent] int
  ,[Professional] int
);

INSERT INTO ExpectedResults
VALUES
   (201805, null, null, 97, 0, 0, 3, 2, 86, 6)
  ,(201805, 03598, null, 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 56401, null, 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 72209, null, 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 85007, 'Phoenix South', 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 85008, 'Phoenix South', 0, 3, 0, 0, 3, 0, 0)
  ,(201805, 85008, 'Phoenix South', 9, 0, 1, 2, 0, 4, 2)
  ,(201805, 85009, 'Phoenix South', 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 85013, 'Phoenix North', 2, 0, 0, 0, 0, 2, 0)
  ,(201805, 85014, 'Phoenix North', 1, 0, 0, 0, 0, 1, 0)
  ,(201805, 85143, 'Pinal', 2, 0, 0, 0, 0, 2, 0)
  ,(201805, 85201, 'Southeast Maricopa', 0, 4, 0, 0, 4, 0, 0)
  ,(201805, 85203, 'Southeast Maricopa', 1, 0, 0, 0, 0, 1, 0)
  ,(201806, null, null, 80, 0, 0, 1, 0, 70, 9)
  ,(201806, 85257, 'East Maricopa', 1, 0, 0, 0, 0, 1, 0)
  ,(201806, 85258, 'East Maricopa', 0, 2, 0, 0, 2, 0, 0)
  ,(201806, 85258, 'East Maricopa', 2, 0, 0, 0, 0, 2, 0)
  ,(201806, 85283, 'East Maricopa', 6, 0, 0, 0, 0, 6, 0);
  
CREATE TABLE CallerTypes
(
   [Id] UNIQUEIDENTIFIER
  ,[Name] VARCHAR(50)
);

INSERT INTO CallerTypes
VALUES
   (NEWID(), 'Business')
  ,(NEWID(), 'Family / Friend')
  ,(NEWID(), 'Other')
  ,(NEWID(), 'Parent')
  ,(NEWID(), 'Professional');

Here I have hidden my current SQL script to pivot the data

SELECT * FROM [InitialData];
//
SELECT * FROM [ExpectedResults];
//
-- What I have tried so far:
DECLARE
   @columns AS NVARCHAR(MAX)
  ,@sql AS NVARCHAR(MAX);
SET @columns = N'';

-- Setup column names using dbo.CallerTypes
SELECT
  @columns += N', PivotResults.' + QUOTENAME(Name)
FROM
(
  SELECT DISTINCT
	[CallerTypes].[Name]
  FROM CallerTypes
  
  INNER JOIN [InitialData]
	ON [CallerTypes].[Name] = [InitialData].[CallerType]
) AS x;

-- Setup Dynamic Pivot Table
SET @sql = N'
  SELECT
	 [PivotResults].[YM]
	,[PivotResults].[Zip]
	,[PivotResults].[Region]
	,[PivotResults].[NewCallers]
	,[PivotResults].[RepeatCallers]
	,' + STUFF(@columns, 1, 2, '') + '
  FROM
  (
	SELECT
	   [CallerTypes].[Name]
	  ,[InitialData].[YM]
	  ,[InitialData].[Zip]
	  ,[InitialData].[Region]
	  ,[InitialData].[NewCallers]
	  ,[InitialData].[RepeatCallers]
	  ,[InitialData].[CallerTypeCount]
	FROM CallerTypes
	
	INNER JOIN [InitialData]
		ON [CallerTypes].[Name] = [InitialData].[CallerType]
  ) AS InnerSelect
  PIVOT (
	SUM([CallerTypeCount])
	FOR [InnerSelect].[Name] IN (' + STUFF(REPLACE(@columns, ', PivotResults.[', ',['), 1, 1, '') + ')
  ) AS PivotResults';
  
 EXEC sp_executesql @sql;

Here is a lightly skimmed down version.
I'm trying to convert this:

+========+=======+===============+============+===============+=================+=================+
|   YM   |  Zip  |    Region     | NewCallers | RepeatCallers |   CallerType    | CallerTypeCount |
+========+=======+===============+============+===============+=================+=================+
| 201805 | null  | null          |          3 |             0 | Family / Friend |               3 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201805 | null  | null          |          2 |             0 | Other           |               2 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201805 | 72209 | null          |          1 |             0 | Parent          |               1 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201805 | 85008 | Phoenix South |          1 |             0 | Parent          |               1 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201805 | 85008 | Phoenix South |          2 |             0 | Family / Friend |               2 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201805 | 85008 | Phoenix South |          0 |             3 | Other           |               3 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201806 | null  | null          |          9 |             0 | Professional    |               9 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201806 | 85258 | East Maricopa |          2 |             0 | Parent          |               2 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+
| 201806 | 85258 | East Maricopa |          0 |             2 | Other           |               2 |
+--------+-------+---------------+------------+---------------+-----------------+-----------------+

Into this:

+========+=======+===============+============+===============+=================+=======+========+==============+
|   YM   |  Zip  |    Region     | NewCallers | RepeatCallers | Family / Friend | Other | Parent | Professional |
+========+=======+===============+============+===============+=================+=======+========+==============+
| 201805 | null  | null          |          5 |             0 |               3 |     2 |      0 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201805 | 72209 | null          |          1 |             0 |               0 |     0 |      1 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201805 | 85008 | Phoenix South |          3 |             0 |               2 |     0 |      1 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201805 | 85008 | Phoenix South |          0 |             3 |               0 |     3 |      0 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201806 | null  | null          |          9 |             0 |               0 |     0 |      0 |            9 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201806 | 85258 | East Maricopa |          2 |             0 |               0 |     0 |      2 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+
| 201806 | 85258 | East Maricopa |          0 |             2 |               0 |     2 |      0 |            0 |
+--------+-------+---------------+------------+---------------+-----------------+-------+--------+--------------+

[InitialData] seems to be okay, I'm struggling to pivot it correctly it I think.

I'm having columns with the same YM, Zip, and Region being spread out when they could have been on a single row. The first four rows are an example of that.

It appears to be separating by the Caller Type and not sharing rows with other Caller Types, but the 11th row (201805, 85008, Phoenix South, 2, 0, null, 2, null, null, 2) goes against that logic, and also displays 2 in the 4th column when in theory it should be 4 (the sum of all Caller Type counts)

This is also wrong because this would be the 12th row in the [ExpectedResults] where the value is combined further into 9).

I only see this happen when all of the Caller Type counts are either null or the same value as each other (2).

My current idea is that I might need to also pivot on both New and Repeat Callers, but I'm not sure how to do that just yet or if it's a valid answer.

Any suggestions?

Upvotes: 1

Views: 95

Answers (1)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Dynamic query for the static version below

(added on 2018-06-22:49 Israel time)

-------------Step 1 ----------------------
-- get the list of [CallerType],
-- which will become our column's names
Declare @ColumnsList1 nvarchar(MAX) = N''
SELECT @ColumnsList1 = 
STUFF(
    (
        SELECT distinct ',' + QUOTENAME([CallerType]) 
        FROM InitialData
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,''
)
-- print @ColumnsList1
-- [Business],[Family / Friend],[Other],[Parent],[Professional]
Declare @ColumnsList2 nvarchar(MAX) = N''
SELECT @ColumnsList2 = 
STUFF(
    (
        SELECT distinct ',
            ISNULL(' + QUOTENAME([CallerType]) + ',0) as '+ QUOTENAME([CallerType])
        FROM InitialData
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,''
)
--print @ColumnsList2
/*
            ISNULL([Business],0) as [Business],
            ISNULL([Family / Friend],0) as [Family / Friend],
            ISNULL([Other],0) as [Other],
            ISNULL([Parent],0) as [Parent],
            ISNULL([Professional],0) as [Professional]
*/
Declare @ColumnsList3 nvarchar(MAX) = N''
SELECT @ColumnsList3 = 
STUFF(
    (
        SELECT distinct '+
            ISNULL(' + QUOTENAME([CallerType]) + ',0)'
        FROM InitialData
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,''
)
-- print @ColumnsList3
/*
            ISNULL([Business],0)+
            ISNULL([Family / Friend],0)+
            ISNULL([Other],0)+
            ISNULL([Parent],0)+
            ISNULL([Professional],0)
*/
-------------Step 2----------------------
DECLARE @MyDynQuery NVARCHAR(MAX) = '
;with MyCTE as (
    select *
    from
    (
      select 
        [YM], [Zip], [Region], [IsNew] = CASE WHEN [NewCallers]>0 then 1 else 0 END, 
        [CallerType], 
        [CallerTypeCount] 
      from InitialData
    ) x
    pivot(
        SUM([CallerTypeCount])
        for [CallerType] in(
            ' + @ColumnsList1 + '
        )
    )p
)
select 
    [YM], [Zip], [Region],--[IsNew] ,
    [NewCallers] = CASE
        WHEN [IsNew] = 1 then 0 +
            ' + @ColumnsList3 + '
        else 0
    END,
    [RepeatCallers] = CASE
        WHEN [IsNew] = 0 then  0 +
            ' + @ColumnsList3 + '
        else 0
    END,
    ' + @ColumnsList2 + '
from MyCTE
'
-- Print @MyDynQuery
execute(@MyDynQuery)
GO

This is a static version of a query - Once confirmed as fit, we will post the dynamic version above

;with MyCTE as (
    select *
    from
    (
      select 
        [YM], [Zip], [Region], [IsNew] = CASE WHEN [NewCallers]>0 then 1 else 0 END, 
        [CallerType], 
        [CallerTypeCount] 
      from InitialData
    ) x
    pivot(
        SUM([CallerTypeCount])
        for [CallerType] in(
            [Family / Friend], [Other], [Parent]
            ,[Professional],[Business]
        )
    )p
)
select 
    [YM], [Zip], [Region],--[IsNew] ,
    [NewCallers] = CASE
        WHEN [IsNew] = 1 then 0 +
            ISNULL([Family / Friend] ,0) + 
            ISNULL([Other]           ,0) + 
            ISNULL([Parent]          ,0) +
            ISNULL([Professional]    ,0) +
            ISNULL([Business]        ,0) 
        else 0
    END,
    [RepeatCallers] = CASE
        WHEN [IsNew] = 0 then  0 +
            ISNULL([Family / Friend] ,0) + 
            ISNULL([Other]           ,0) + 
            ISNULL([Parent]          ,0) +
            ISNULL([Professional]    ,0) +
            ISNULL([Business]        ,0)
        else 0
    END,
            ISNULL([Family / Friend] ,0) [Family / Friend], 
            ISNULL([Other]           ,0) [Other]          , 
            ISNULL([Parent]          ,0) [Parent]         ,
            ISNULL([Professional]    ,0) [Professional]   ,
            ISNULL([Business]        ,0) [Business]       
from MyCTE
GO

Update information above this line 2018-06-29 21:45 Israel time

Good day Ryan,

Now that I have DDL+DML i can provide a solution pretty fast (assuming I understand the request) :-)

Please check if below queries solve your needs. First when we need dynamic query, I write a simple static pivot query. Only once I think that I got the right solution I move to dynamic query, which is the second query bellow

;with MyCTE as (
    select *
    from
    (
      select 
        [YM], [Zip], [Region], 
        [NewCallers], [RepeatCallers], [CallerType], 
        [CallerTypeCount] 
      from InitialData
    ) x
    pivot(
        SUM([CallerTypeCount])
        for [CallerType] in(
            [Family / Friend], [Other], [Parent]
            ,[Professional],[Business]
        )
    )p
)
select 
    [YM], [Zip], [Region], 
    SUM(ISNULL([NewCallers]      ,0)), 
    SUM(ISNULL([RepeatCallers]   ,0)),
    SUM(ISNULL([Family / Friend] ,0)), 
    SUM(ISNULL([Other]           ,0)), 
    SUM(ISNULL([Parent]          ,0)),
    SUM(ISNULL([Professional]    ,0)),
    SUM(ISNULL([Business]        ,0))
from MyCTE
group by [YM], [Zip], [Region]
GO

And now assuming the above query works well, we can move to the write a dynamic query.

------------------------------------------------------------
-- Now let's do it in dynamic pivot
-------------Step 1 ----------------------
-- get the list of [CallerType],
-- which will become our column's names
Declare @ColumnsList1 nvarchar(MAX) = N''
SELECT @ColumnsList1 = 
STUFF(
    (
        SELECT distinct ',' + QUOTENAME([CallerType]) 
        FROM InitialData
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,''
)
Declare @ColumnsList2 nvarchar(MAX) = N''
SELECT @ColumnsList2 = 
replace (@ColumnsList1,'[','
SUM(ISNULL([')
SELECT @ColumnsList2 = REPLACE(@ColumnsList2,'],','],0)),') + N',0))'
-- print @ColumnsList2
-------------Step 2----------------------
-- Back to the suery we replace the column with the parameter @ColumnsList
DECLARE @MyDynQuery NVARCHAR(MAX) = 
';with MyCTE as (
    select *
    from
    (
      select 
        [YM], [Zip], [Region], 
        [NewCallers], [RepeatCallers], [CallerType], 
        [CallerTypeCount] 
      from InitialData
    ) x
    pivot(
        SUM([CallerTypeCount])
        for [CallerType] in(
            ' + @ColumnsList1 + '
        )
    )p
)
select 
    [YM], [Zip], [Region], 
    SUM(ISNULL([NewCallers]      ,0)), 
    SUM(ISNULL([RepeatCallers]   ,0)),
    ' + @ColumnsList2 + '
from MyCTE
group by [YM], [Zip], [Region]
'
-- Print @MyDynQuery
execute(@MyDynQuery)
GO

Upvotes: 1

Related Questions