Result set in pivot format

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

Answer 1

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]

Answer 2

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.

Answer 3

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.

Answer 4

User-807418713 posted

Thank You

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

It would be needful.

Thanking You

Answer 5

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]

Answer 6

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.

Answer 7

User-807418713 posted

Thank You @Limno