Answered by:
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
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