locked
GET SQL RESUSET WITH PIVOT AND SMALL RRS feed

  • 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