locked
Dynamic Pivot in SQL with ROW and COLUMN TOTAL RRS feed

  • Question

  • User2101416554 posted
    My Table
    
    CREATE TABLE [dbo].[ShoppingDetail](
    	[CustomerName] [varchar](256) NOT NULL,
    	[ProductCategory] [varchar](256) NOT NULL,
    	[TotalAmount] [numeric](18, 2) NOT NULL,
    	[Total Value] [numeric](18, 2) NULL,
    	[Date] [date] NULL
    ) ON [PRIMARY]
    GO
    Table Data
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(1500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(2500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(4000.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(5000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(7000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(6500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(3200.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(15783.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-14' AS Date))
    

    Dynamic Pivot Query for Date column

    DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
    DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
    DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
    --Extarct unique customer names with pivot formattings
    SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-'), '') + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
    SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
    --Generate column names to be put in SELECT list with NULL handling and aliases also
    SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') , '') + '], 0) AS [' + REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') + ']'
    FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
    --Generate dynamic PIVOT query here
    SET @SQLStatement =
    N'SELECT CustomerName,ProductCategory '
    + @PivotColumnsToSelect +
    '
    FROM dbo.ShoppingDetail
    PIVOT
    (
    SUM(TotalAmount) FOR
    [Date] IN
    (' + @UniqueCustomersToPivot + ')
    ) AS PVT Order By CustomerName
    '
    --Execute the dynamic t-sql PIVOT query below
    EXEC (@SQLStatement)

    The above query is working fine ... 

    But i need the below

    1. I need Row and Column of this Pivot
    2. How to add where condition like TotalAmount is not null
    Friday, December 27, 2019 5:45 AM

Answers

  • User77042963 posted
    CREATE TABLE [dbo].[ShoppingDetail](
    	[CustomerName] [varchar](256) NOT NULL,
    	[ProductCategory] [varchar](256) NOT NULL,
    	[TotalAmount] [numeric](18, 2) NOT NULL,
    	[Date] [date] NULL
    )
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=7  --day
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
    from(Select n from nums) D(n)
    )
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
    FROM  dates   
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
    ,
    @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
    FROM  dates   
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
     
     From dates t1
    
    
      
    SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
    +' from [ShoppingDetail]
    Group by grouping sets(CustomerName,())  '
      
    --print @sql
     EXEC sp_executesql @sql;
         
    
    drop table [ShoppingDetail]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 27, 2019 4:02 PM

All replies

  • User2101416554 posted

    My Table
    
    CREATE TABLE [dbo].[ShoppingDetail](
    	[CustomerName] [varchar](256) NOT NULL,
    	[ProductCategory] [varchar](256) NOT NULL,
    	[TotalAmount] [numeric](18, 2) NOT NULL,
    	[Total Value] [numeric](18, 2) NULL,
    	[Date] [date] NULL
    ) ON [PRIMARY]
    GO
    Table Data
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(1500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(2500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(4000.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(5000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(7000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(6500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(3200.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(15783.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-14' AS Date))

    Dynamic Pivot Query for Date column

    DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
    DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
    DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
    --Extarct unique customer names with pivot formattings
    SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-'), '') + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
    SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
    --Generate column names to be put in SELECT list with NULL handling and aliases also
    SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') , '') + '], 0) AS [' + REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') + ']'
    FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
    --Generate dynamic PIVOT query here
    SET @SQLStatement =
    N'SELECT CustomerName,ProductCategory '
    + @PivotColumnsToSelect +
    '
    FROM dbo.ShoppingDetail
    PIVOT
    (
    SUM(TotalAmount) FOR
    [Date] IN
    (' + @UniqueCustomersToPivot + ')
    ) AS PVT Order By CustomerName
    '
    --Execute the dynamic t-sql PIVOT query below
    EXEC (@SQLStatement)

    The above query is working fine ... 

    But i need the below

    1. I need Row and Column of this Pivot
    2. How to add where condition like TotalAmount is not null

    How to add more column is select query with the given below link. Like personname,pincode etc

    I got the solution from this link
    
    https://www.aspforums.net/Threads/165889/Calculate-Row-Total-and-Column-Total-using-dynamic-Pivot-query-in-SQL-Server/
    
    Friday, December 27, 2019 11:18 AM
  • User77042963 posted
    CREATE TABLE [dbo].[ShoppingDetail](
    	[CustomerName] [varchar](256) NOT NULL,
    	[ProductCategory] [varchar](256) NOT NULL,
    	[TotalAmount] [numeric](18, 2) NOT NULL,
    	[Date] [date] NULL
    )
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=7  --day
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
    from(Select n from nums) D(n)
    )
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
    FROM  dates   
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
    ,
    @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
    FROM  dates   
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
     
     From dates t1
    
    
      
    SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
    +' from [ShoppingDetail]
    Group by grouping sets(CustomerName,())  '
      
    --print @sql
     EXEC sp_executesql @sql;
         
    
    drop table [ShoppingDetail]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 27, 2019 4:02 PM
  • User77042963 posted

    If you are using SQL Server 2017 or 2019, you can use string_agg function to build the dynamic column list.

    CREATE TABLE [dbo].[ShoppingDetail](
    	[CustomerName] [varchar](256) NOT NULL,
    	[ProductCategory] [varchar](256) NOT NULL,
    	[TotalAmount] [numeric](18, 2) NOT NULL,
    	[Date] [date] NULL
    )
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    , (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    ,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    ,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    , (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=7  --day
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
    from(Select n from nums) D(n)
    )
     
    Select @ColumnHeaders = string_agg('SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  , ','+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
    ,@ColumnHeaders2 = string_agg('isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) ' , '+'+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
    From dates  
    
    SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
    +' from [ShoppingDetail]
    Group by grouping sets(CustomerName,())  '
      
     EXEC sp_executesql @sql;
         
    
    drop table [ShoppingDetail]

    Friday, December 27, 2019 5:07 PM