Search code examples
sqldynamicpivotunpivot

Dynamic sql with pivot

enter image description here

i have the data like in the image..

the types will change as per the type ID selection,

i am trying to achieve and output similar to below:-

enter image description here

The 166su01count corresponds to type 166su01 and it should show 5(daystotal) for date 4/16/2018

The 166su01Rate corresponds to type 166su01 and it should show 60.00(percount) for date 4/16/2018

The 166su02count corresponds to type 166su02 and it should show 7(daystotal) for date 4/16/2018

The 166su02Rate corresponds to type 166su02 and it should show 28.57(percount) for date 4/16/2018 and so on so forth...

the types change based on selection please help..

Solution

  • Providing solution for MS-SQL Server -

    Creating the tables and data - 

    CREATE TABLE [dbo].[Type](
    [daynumbers] [date] NULL,
    [daystotal] [int] NULL,
    [types] [varchar](15) NULL,
    [percount] [float] NULL
) ON [PRIMARY]


INSERT INTO [dbo].[Type]
           ([daynumbers]
           ,[daystotal]
           ,[types]
           ,[percount])
     VALUES
('2018-04-16',  5   ,'166su01', 60)
,('2018-04-16', 7   ,'166su02', 28.57)
,('2018-04-18', 8   ,'166su02', 62.5)
,('2018-04-19', 5   ,'166su01', 100)

    There are two parts to the problem -

    1. Doing multiple pivots(one for percount and one for daystotal ) from a single columnn(types)
    2. Making the query applicable to dynamic sql

    Part 1 -

    This can be solved by duplicating types column and naming it as types1 and pivoting the table twice - One for percount and one for daystotal

    SELECT *
FROM
(SELECT *,types+' Count'[types1] FROM dbo.Type) AS SourceTable --duplicate column created here

PIVOT  
(  
SUM(percount) 
FOR types IN ([166su01], [166su02])  
) AS PivotTable
PIVOT  
(  
SUM(daystotal) 
FOR types1 IN ([166su01 Count], [166su02 Count])  
) AS PivotTable

    This will create extra rows So for grouping them using daynumbers column in the following way - 

    SELECT daynumbers,Sum([166su01])[166su01],Sum( [166su01 Count]) [166su01 Count], Sum([166su02])[166su02],Sum( [166su02 Count]) [166su02 Count] FROM  
(SELECT *,types+' Count'[types1] FROM dbo.Type) AS SourceTable

PIVOT  
(  
SUM(percount) 
FOR types IN ([166su01], [166su02])  
) AS PivotTable
PIVOT  
(  
SUM(daystotal) 
FOR types1 IN ([166su01 Count], [166su02 Count])  
) AS PivotTable
group by
daynumbers

    Part 2 - Making this query dynamic Need to dynamically define the following items -

    1. Headers of Select Query - Sum([166su01])[166su01],Sum( [166su01 Count]) [166su01 Count], Sum([166su02])[166su02],Sum( [166su02 Count]) [166su02 Count]
    2. Percount Pivot - [166su01], [166su02]
    3. DaysTotal Pivot - [166su01 Count], [166su02 Count]

    This can be done by - 

    DECLARE @PivotPercount NVARCHAR(MAX) = N'' --Variable to hold types to pivot for percount
SELECT @PivotPercount = @PivotPercount + ', [' + COALESCE(types, '') + ']' FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @PivotPercount = LTRIM(STUFF(@PivotPercount, 1, 1, '')) --Remove first comma and space
print @PivotPercount

DECLARE @PivotDaysTotal NVARCHAR(MAX) = N'' --Variable to hold types to pivot for daystotal
SELECT @PivotDaysTotal = @PivotDaysTotal + ', [' + COALESCE(types, '') + ' Count]' FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @PivotDaysTotal = LTRIM(STUFF(@PivotDaysTotal, 1, 1, '')) --Remove first comma and space
print @PivotDaysTotal

DECLARE @Headers NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
SELECT @Headers = @Headers + ', Sum([' + COALESCE(types, '') + '])'+'[' + COALESCE(types, '') + ']' + ',Sum( [' + COALESCE(types, '') + ' Count]) ' + '[' + COALESCE(types, '') + ' Count]'   FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @Headers = LTRIM(STUFF(@Headers, 1, 1, '')) --Remove first comma and space
print @Headers

    Now Combining Everything - Final Query -

    DECLARE @PivotPercount NVARCHAR(MAX) = N'' --Variable to hold types to pivot for percount
SELECT @PivotPercount = @PivotPercount + ', [' + COALESCE(types, '') + ']' FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @PivotPercount = LTRIM(STUFF(@PivotPercount, 1, 1, '')) --Remove first comma and space
print @PivotPercount

DECLARE @PivotDaysTotal NVARCHAR(MAX) = N'' --Variable to hold types to pivot for daystotal
SELECT @PivotDaysTotal = @PivotDaysTotal + ', [' + COALESCE(types, '') + ' Count]' FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @PivotDaysTotal = LTRIM(STUFF(@PivotDaysTotal, 1, 1, '')) --Remove first comma and space
print @PivotDaysTotal

DECLARE @Headers NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
SELECT @Headers = @Headers + ', Sum([' + COALESCE(types, '') + '])'+'[' + COALESCE(types, '') + ']' + ',Sum( [' + COALESCE(types, '') + ' Count]) ' + '[' + COALESCE(types, '') + ' Count]'   FROM (SELECT DISTINCT types FROM dbo.Type)DT
SELECT @Headers = LTRIM(STUFF(@Headers, 1, 1, '')) --Remove first comma and space
print @Headers

DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT daynumbers,' + @Headers +
' FROM  
(SELECT *,types+'' Count''[types1] FROM dbo.Type) AS SourceTable

PIVOT  
(  
SUM(percount) 
FOR types IN ('+ @PivotPercount +')  
) AS PivotTable
PIVOT  
(  
SUM(daystotal) 
FOR types1 IN (' + @PivotDaysTotal + ')  
) AS PivotTable
group by
daynumbers
'
print @SQLStatement
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)

    Please vote for the answer if you find it useful