locked
Result set in pivot format RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    Dep Name Process_Date Qty
    AA 01-May-2020 10
    BB 01-May-2020 5
    AA 02-May-2020 4
    AA 02-May-2020 2
    AA 03-May-2020 10
    BB 03-May-2020 20
    CC 03-May-2020 30
    CC 03-May-2020 5
    CC 03-May-2020 10

    This is my code

    USE [master]
    GO
    /****** Object:  Table [dbo].[Temp1]    Script Date: 05/12/2020 15:48:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Temp1](
    	[Dep_Name] [varchar](50) NULL,
    	[Process_Date] [datetime] NULL,
    	[Qty] [float] NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'AA', CAST(0x0000ABAE00000000 AS DateTime), 10)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'BB', CAST(0x0000ABAE00000000 AS DateTime), 5)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'AA', CAST(0x0000ABAF00000000 AS DateTime), 4)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'AA', CAST(0x0000ABAF00000000 AS DateTime), 2)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'AA', CAST(0x0000ABB000000000 AS DateTime), 10)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'BB', CAST(0x0000ABB000000000 AS DateTime), 20)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'CC', CAST(0x0000ABB000000000 AS DateTime), 30)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'CC', CAST(0x0000ABB000000000 AS DateTime), 5)
    INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES (N'CC', CAST(0x0000ABB000000000 AS DateTime), 10)
    

    I want output like this if i pass 01-May-2020 to 03-May-2020

    Dep Name 01-May-2020 02-May-2020 03-May-2020 Total Qty
    AA 10 6 10 26
    BB 5 0 20 25
    CC 0 0 50 50
    Total 15 6 80 101

    How Would Be SQL Query

    Thanking You

    Tuesday, May 12, 2020 10:19 AM

Answers

  • User77042963 posted
    CREATE TABLE [dbo].[Temp1](
    	[Dep_Name] [varchar](50) NULL,
    	[Process_Date] [datetime] NULL,
    	[Qty] [float] NULL
    ) 
     INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES
     (N'AA', CAST(0x0000ABAE00000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABAE00000000 AS DateTime), 5)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 4)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 2)
     ,(N'AA', CAST(0x0000ABB000000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABB000000000 AS DateTime), 20)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 30)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 5)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 10)
     
    declare @fromdate date='01-May-2020'
    declare @todate date='03-May-2020'
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    Declare @sqlPivot as NVarchar(4000)
     
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end) as ' + Quotename( format([Process_Date],'dd-MMM-yyyy'),'[')  + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1]
    where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    Select @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end),0) '   + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1] where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     
    set @sqlPivot=' Select  
    Case when t.[Dep_Name] is null then ''Total'' else t.[Dep_Name] end [Dep_Name]
      ,'+   @ColumnHeaders + ','+@ColumnHeaders2+' as [Total Qty]
    FROM    [Temp1] t 
    Group by GROUPING SETS ((t.[Dep_Name]),())
     '
     --print @ColumnHeaders2
     EXEC sp_executesql @sqlPivot;
     
    --clean up
     drop table   [dbo].[Temp1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2020 2:36 PM
  • User77042963 posted
    CREATE TABLE [dbo].[Temp1](
    	[Dep_Name] [varchar](50) NULL,
    	[Process_Date] [datetime] NULL,
    	[Qty] [float] NULL
    ) 
     INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES
     (N'AA', CAST(0x0000ABAE00000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABAE00000000 AS DateTime), 5)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 4)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 2)
     ,(N'AA', CAST(0x0000ABB000000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABB000000000 AS DateTime), 20)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 30)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 5)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 10)
     
    declare @fromdate date='01-May-2020'
    declare @todate date='03-May-2020'
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    Declare @sqlPivot as NVarchar(4000)
     
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end) as ' + Quotename( Replace(convert(varchar(11),[Process_Date],106),' ','-'),'[')  + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1]
    where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    Select @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end),0) '   + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1] where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     
    set @sqlPivot=' Select  
    Case when t.[Dep_Name] is null then ''Total'' else t.[Dep_Name] end [Dep_Name]
      ,'+   @ColumnHeaders + ','+@ColumnHeaders2+' as [Total Qty]
    FROM    [Temp1] t 
    Group by GROUPING SETS ((t.[Dep_Name]),())
     '
     --print @ColumnHeaders2
     EXEC sp_executesql @sqlPivot;
     
    --clean up
     drop table   [dbo].[Temp1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2020 5:05 PM

All replies

  • User77042963 posted
    CREATE TABLE [dbo].[Temp1](
    	[Dep_Name] [varchar](50) NULL,
    	[Process_Date] [datetime] NULL,
    	[Qty] [float] NULL
    ) 
     INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES
     (N'AA', CAST(0x0000ABAE00000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABAE00000000 AS DateTime), 5)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 4)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 2)
     ,(N'AA', CAST(0x0000ABB000000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABB000000000 AS DateTime), 20)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 30)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 5)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 10)
     
    declare @fromdate date='01-May-2020'
    declare @todate date='03-May-2020'
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    Declare @sqlPivot as NVarchar(4000)
     
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end) as ' + Quotename( format([Process_Date],'dd-MMM-yyyy'),'[')  + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1]
    where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    Select @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end),0) '   + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1] where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     
    set @sqlPivot=' Select  
    Case when t.[Dep_Name] is null then ''Total'' else t.[Dep_Name] end [Dep_Name]
      ,'+   @ColumnHeaders + ','+@ColumnHeaders2+' as [Total Qty]
    FROM    [Temp1] t 
    Group by GROUPING SETS ((t.[Dep_Name]),())
     '
     --print @ColumnHeaders2
     EXEC sp_executesql @sqlPivot;
     
    --clean up
     drop table   [dbo].[Temp1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2020 2:36 PM
  • User-807418713 posted

    Hello limno

    Thank You For Your Code

    I got this error

    Msg 195, Level 15, State 10, Line 8
    'format' is not a recognized built-in function name.

    Tuesday, May 12, 2020 4:51 PM
  • User77042963 posted

    Either you are working on SQL Server 2008 or your database  comparability level is not above 100 (should SQ: Server 2012 or above to use format function.

    Tuesday, May 12, 2020 5:00 PM
  • User-807418713 posted

    Thank You

    Its my kind request will you please convert the above code to 2008?

    It would be needful.

    Thanking You

    Tuesday, May 12, 2020 5:04 PM
  • User77042963 posted
    CREATE TABLE [dbo].[Temp1](
    	[Dep_Name] [varchar](50) NULL,
    	[Process_Date] [datetime] NULL,
    	[Qty] [float] NULL
    ) 
     INSERT [dbo].[Temp1] ([Dep_Name], [Process_Date], [Qty]) VALUES
     (N'AA', CAST(0x0000ABAE00000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABAE00000000 AS DateTime), 5)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 4)
     ,(N'AA', CAST(0x0000ABAF00000000 AS DateTime), 2)
     ,(N'AA', CAST(0x0000ABB000000000 AS DateTime), 10)
     ,(N'BB', CAST(0x0000ABB000000000 AS DateTime), 20)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 30)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 5)
     ,(N'CC', CAST(0x0000ABB000000000 AS DateTime), 10)
     
    declare @fromdate date='01-May-2020'
    declare @todate date='03-May-2020'
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 NVARCHAR(4000) ;
    Declare @sqlPivot as NVarchar(4000)
     
     
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end) as ' + Quotename( Replace(convert(varchar(11),[Process_Date],106),' ','-'),'[')  + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1]
    where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    Select @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Process_Date]='+ quotename( Convert(char(8),[Process_Date],112),'''') +' then  [Qty]  else null end),0) '   + char(10)+char(13)
    FROM  (select distinct [Process_Date] from [Temp1] where [Process_Date]>=@fromdate and [Process_Date]<=@todate)  t
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     
    set @sqlPivot=' Select  
    Case when t.[Dep_Name] is null then ''Total'' else t.[Dep_Name] end [Dep_Name]
      ,'+   @ColumnHeaders + ','+@ColumnHeaders2+' as [Total Qty]
    FROM    [Temp1] t 
    Group by GROUPING SETS ((t.[Dep_Name]),())
     '
     --print @ColumnHeaders2
     EXEC sp_executesql @sqlPivot;
     
    --clean up
     drop table   [dbo].[Temp1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2020 5:05 PM
  • User77042963 posted

    Thank You

    Its my kind request will you please convert the above code to 2008?

    It would be needful.

    Thanking You

    Still using SQLServer 2008 in 2020 is way behind. It is not a supported product long time ago.

    Tuesday, May 12, 2020 5:06 PM
  • User-807418713 posted

    Thank You @Limno

    Tuesday, May 12, 2020 5:08 PM