locked
Dynamic Pivot for Date Column with Day and Weekno RRS feed

  • Question

  • User2101416554 posted

    The thread https://forums.asp.net/t/2162731.aspx?Dynamic+Pivot+in+SQL+with+ROW+and+COLUMN+TOTAL

    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]

    The code is working fine i would like to have dynamic column date, day and week like below

    CustomerName 8-Dec-2019 9-Dec-2019 10-Dec-2019 11-Dec-2019 12-Dec-2019 13-Dec-2019 14-Dec-2019 Total
    Day Sunday Monday Tuesday Wednesday Thursday Friday Saturday  
    Week Week49 Week50 Week50 Week50 Week50 Week50 Week50  
    Caitlin C Watson NULL NULL NULL 3289 NULL NULL 75769 79058
    Roy Martin NULL NULL 5425 NULL NULL NULL NULL 5425
    Taylor Torres NULL NULL 6567 46897 NULL NULL 15783 69247
    Total NULL NULL 11992 50186 NULL NULL 91552 153730
    Saturday, December 28, 2019 3:50 AM

Answers

  • User2101416554 posted

    Follow this link 

    https://forums.asp.net/t/2162752.aspx?Dynamic+Pivot+alais+name+for+dynamic+column+

    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)+'('+datename(weekday,dt)+',week'+datename(ISO_WEEK,dt)+')','[') + 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
    Tuesday, December 31, 2019 7:13 AM

All replies

  • User-719153870 posted

    Hi umerfaiz123,

    The code is working fine i would like to have dynamic column date, day and week like below

    CustomerName 8-Dec-2019 9-Dec-2019 10-Dec-2019 11-Dec-2019 12-Dec-2019 13-Dec-2019 14-Dec-2019 Total
    Day Sunday Monday Tuesday Wednesday Thursday Friday Saturday  
    Week Week49 Week50 Week50 Week50 Week50 Week50 Week50  
    Caitlin C Watson NULL NULL NULL 3289 NULL NULL 75769 79058
    Roy Martin NULL NULL 5425 NULL NULL NULL NULL 5425
    Taylor Torres NULL NULL 6567 46897 NULL NULL 15783 69247
    Total NULL NULL 11992 50186 NULL NULL 91552 153730

    According to the expected table, we won't suggest you do this in SQL query, this can easily been done with js or any other programing language but it will take a lot of time trying to achieve this in pure SQL query.

    It's not allowed having different data types in the same column, such as 'Week50' and 3289. To show like this, you will need to convert all your numeric data to nvarchar type which you might need to select your current query result into a table and then alter the table columns.

    If you insist, i can provide a way of thinking:

    1. Use SQL SELECT INTO Statement to select your current query result into a table.
    2. Convert the data type of all your new table fields, you can refer to SQL ALTER TABLE Statement and Alter All Columns datatype in SQL Server.
    3. Use DATEPART to get the Week value, for example, select 'Week'+convert(varchar(10),DATEPART ( ww , '2019-12-09' ))
    4. Use DATENAME to get the week of day, for example, select DATENAME(WEEKDAY, '2019-12-09').
    5. Use SQL UNION and UNION ALL Keywords to show all your final result together.

    As i said, this could be hard working in pure SQL query that you better doing this in your program.

    Best Regard,

    Yang Shen

    Monday, December 30, 2019 2:51 AM
  • User2101416554 posted

    Follow this link 

    https://forums.asp.net/t/2162752.aspx?Dynamic+Pivot+alais+name+for+dynamic+column+

    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)+'('+datename(weekday,dt)+',week'+datename(ISO_WEEK,dt)+')','[') + 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
    Tuesday, December 31, 2019 7:13 AM