Remove From My Forums

GET SQL RESUSET WITH PIVOT AND SMALL

Question

User-807418713 posted

Hello

This is my table data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tempnew_demo](
	[Details] [varchar](50) NULL,
	[Type] [varchar](50) NULL,
	[Condition] [varchar](50) NULL,
	[Origin] [varchar](50) NULL,
	[GRADE] [varchar](510) NULL,
	[SIZE] [varchar](50) NULL,
	[field1] [float] NULL,
	[field2] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tempnew_demo] ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'AA - BB - KK', N'AA', N'BB', N'KK', N'Z', N'HEAVY', 10, 50)
INSERT [dbo].[tempnew_demo] ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'MM - NN - YY', N'MM', N'NN', N'YY', N'MM', N'MEDIUM', 200, 800)
INSERT [dbo].[tempnew_demo] ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'KK - CC - DD', N'KK', N'CC', N'DD', N'XXYY', N'MEDIUM', 5, 25)
INSERT [dbo].[tempnew_demo] ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'VV - FF - RR', N'VV', N'FF', N'RR', N'W', N'SMALL', 40, 150)
INSERT [dbo].[tempnew_demo] ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'VV - FF - RR', N'VV', N'FF', N'RR', N'XXYY', N'SMALL', 1, 5)

I want output like this as below using sql 2008

Thanking you

Thursday, January 14, 2021 11:54 AM

Answers

User-1330468790 posted

Hi Gopi.MCA,

I think the columns could vary for the real scenario so that I provide you with a dynamic version of SQL statement.

The keys are:

Dynamically select out all of [GRADE] + field combinations for the final column row
CROSS APPLY to make the columns with values in rows
PIVOT to change rows to columns

SQL statement:

DROP TABLE IF EXISTS #tempnew_demo

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE #tempnew_demo (
	[Details] [varchar](50) NULL,
	[Type] [varchar](50) NULL,
	[Condition] [varchar](50) NULL,
	[Origin] [varchar](50) NULL,
	[GRADE] [varchar](510) NULL,
	[SIZE] [varchar](50) NULL,
	[field1] [float] NULL,
	[field2] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT #tempnew_demo ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'AA - BB - KK', N'AA', N'BB', N'KK', N'Z', N'HEAVY', 10, 50)
INSERT #tempnew_demo ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'MM - NN - YY', N'MM', N'NN', N'YY', N'MM', N'MEDIUM', 200, 800)
INSERT #tempnew_demo ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'KK - CC - DD', N'KK', N'CC', N'DD', N'XXYY', N'MEDIUM', 5, 25)
INSERT #tempnew_demo ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'VV - FF - RR', N'VV', N'FF', N'RR', N'W', N'SMALL', 40, 150)
INSERT #tempnew_demo ([Details], [Type], [Condition], [Origin], [GRADE], [SIZE], [field1], [field2]) VALUES (N'VV - FF - RR', N'VV', N'FF', N'RR', N'XXYY', N'SMALL', 1, 5)

DECLARE 
    @col_list varchar(max),
	@sql varchar(max);


set @col_list = stuff((select distinct ','+QUOTENAME(Rtrim([GRADE]) + ' field1')+','+QUOTENAME(Rtrim([GRADE]) + ' field2') from #tempnew_demo for xml path('')),1,1,'')

print @col_list

set @sql = '
SELECT Details, '+@col_list+', [Total field1], [Total field2]
FROM   (SELECT t.Details + '' - '' + t.SIZE AS [Details],
               [Total field1],
               [Total field2],
               C.*
        FROM   (SELECT [Total field1] = Sum(field1)OVER(partition BY [Details]),
                       [Total field2] = Sum(field2)OVER(partition BY [Details]),*
                FROM   #tempnew_demo) t
               CROSS APPLY ( VALUES(Rtrim([GRADE]) + '' field1'',field1),
                                   (Rtrim([GRADE]) + '' field2'',field2) ) C (Col, Value)) src
       PIVOT ( Sum([Value])
             FOR [Col] IN ('+@col_list+') ) pvt ' 

print @sql
exec (@sql)

Result:

Hope helps.

Best regards,

Sean

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Friday, January 15, 2021 9:53 AM

Answered by:

GET SQL RESUSET WITH PIVOT AND SMALL

Question

Answers