Adding vertical sum of rows(Sum in last availble column) for Transposed data

Sameer · January 1, 2022, 6:45am

Hi Experts ,
Happy New Year!
The below script works fine dynamically transpose the data , I have a requirement to add two columns and get sum of Data1 columns in the last available column Same as for Data2 the result are in screenshot. May be from group by rollup i dont know how to do it.
Secondly i am trying to run a stored procedure by providing the @Startdate ( 10-Oct-2021) and @Endate (11-Oc-2021) , but the script retrieve data all available rows in the database not between parameters passing, please advise what i am doing wrong.

Current results

Required results.

   IF OBJECT_ID('tempdb..##Table') IS NOT NULL DROP TABLE [##Table]	
    GO	
    SELECT * INTO [##Table] FROM (SELECT CAST('10-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName1' AS VARCHAR(1000)) AS [ItemName], CAST('1336.9' AS FLOAT) AS [Data1], CAST('0.85623' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('10-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName2' AS VARCHAR(1000)) AS [ItemName], CAST('318.62' AS FLOAT) AS [Data1], CAST('0.31913' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('10-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName3' AS VARCHAR(1000)) AS [ItemName], CAST('40' AS FLOAT) AS [Data1], CAST('50' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('10-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName4' AS VARCHAR(1000)) AS [ItemName], CAST('1068.51' AS FLOAT) AS [Data1], CAST('7.23771' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('10-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName5' AS VARCHAR(1000)) AS [ItemName], CAST('238.51' AS FLOAT) AS [Data1], CAST('3.56735' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('11-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName1' AS VARCHAR(1000)) AS [ItemName], CAST('10' AS FLOAT) AS [Data1], CAST('20' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('11-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName2' AS VARCHAR(1000)) AS [ItemName], CAST('11' AS FLOAT) AS [Data1], CAST('21' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('11-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName3' AS VARCHAR(1000)) AS [ItemName], CAST('12' AS FLOAT) AS [Data1], CAST('22' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('11-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName4' AS VARCHAR(1000)) AS [ItemName], CAST('13' AS FLOAT) AS [Data1], CAST('23' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('11-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName5' AS VARCHAR(1000)) AS [ItemName], CAST('14' AS FLOAT) AS [Data1], CAST('24' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('12-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName1' AS VARCHAR(1000)) AS [ItemName], CAST('10' AS FLOAT) AS [Data1], CAST('20' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('12-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName2' AS VARCHAR(1000)) AS [ItemName], CAST('11' AS FLOAT) AS [Data1], CAST('21' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('12-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName3' AS VARCHAR(1000)) AS [ItemName], CAST('12' AS FLOAT) AS [Data1], CAST('22' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('12-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName4' AS VARCHAR(1000)) AS [ItemName], CAST('13' AS FLOAT) AS [Data1], CAST('23' AS FLOAT) AS [Data2] UNION ALL 	
    SELECT CAST('12-Oct-2021 00:00:00' AS DATETIME) AS [Startdate], CAST('ItemName5' AS VARCHAR(1000)) AS [ItemName], CAST('14' AS FLOAT) AS [Data1], CAST('24' AS FLOAT) AS [Data2] ) a	



    Declare    @sql    nvarchar(max)
    Declare @StartDate DATETIME
    Declare @EndDate DATETIME


    Set @StartDate = '10-OCT-2021' 
    Set @EndDate =  '11-OCT-2021' 
    select    @sql    = isnull(@sql + ',', N'Select    [Startdate],') + char(13)
            + N'    [ItemName]  = max(Case when [ItemName] = ''' + [ItemName] + ''' THEN [ItemName]  end),' + char(13)
            + N'    [Data1] = max(Case when [ItemName] = ''' + [ItemName] + ''' THEN [Data1] end),' + char(13)
            + N'    [Data2] = max(Case when [ItemName] = ''' + [ItemName] + ''' THEN [Data2] end)'  + char(13)
    from    ##Table where Startdate>=@StartDate and Startdate<=@Enddate

    group by [ItemName]    
    order by [ItemName]    

    select    @sql    = @sql
            + N'from    [##Table]' + char(13) 
            + N'group by   [Startdate]'

    print    @sql
    exec    sp_executesql @sql

JeffModen · January 2, 2022, 12:30am

Ok... good to go... I had missed a requirement and needed to repair and repost the code below. Works fine with the start and end date parameters now.

YOU DID A TOTALLY AWESOME JOB ON POST! Very well done, especially with the test data and the Before'n'After desired results!

Here's a way to significantly reduce the size of the code for the test data as well as making it a little easier for folks to eyeball. It also uses a "regular" Temp Table instead of a "Global" Temp Table just to make sure that it doesn't interfere with anyone else's testing.

   DROP TABLE IF EXISTS #Table;
GO	
 SELECT  StartDate = CONVERT(DATETIME     ,v.StartDate)
        ,ItemName  = CONVERT(VARCHAR(1000),v.ItemName)
        ,Data1     = CONVERT(FLOAT        ,v.Data1)
        ,Data2     = CONVERT(FLOAT        ,v.Data2)
   INTO #Table
   FROM (VALUES
         ('10-Oct-2021','ItemName1',1336.9 ,0.85623)
        ,('10-Oct-2021','ItemName2',318.62 ,0.31913)
        ,('10-Oct-2021','ItemName3',40     ,50     )
        ,('10-Oct-2021','ItemName4',1068.51,7.23771)
        ,('10-Oct-2021','ItemName5',238.51 ,3.56735)
        ,('11-Oct-2021','ItemName1',10     ,20     )
        ,('11-Oct-2021','ItemName2',11     ,21     )
        ,('11-Oct-2021','ItemName3',12     ,22     )
        ,('11-Oct-2021','ItemName4',13     ,23     )
        ,('11-Oct-2021','ItemName5',14     ,24     )
        ,('12-Oct-2021','ItemName1',10     ,20     )
        ,('12-Oct-2021','ItemName2',11     ,21     )
        ,('12-Oct-2021','ItemName3',12     ,22     )
        ,('12-Oct-2021','ItemName4',13     ,23     )
        ,('12-Oct-2021','ItemName5',14     ,24     )
        )v(StartDate,ItemName,Data1,Data2)
;

You also did a good thing with the SELECT STARTDATE line and the ISNULL. I enhanced that a bit. Here's the reason...

You wanted the line totals and that was pretty easy to add. I also changed the MAX()'s to SUM(s) just to bullet proof the code if there was more than one original row of data with the same date and changed your StartDate line to do "whole date" conversions just in case someone slipped in some times into the StartDate column.

Now, you've just gotta know that the next thing people would ask is "Sameer? Can you please add a 'Total" line to this report?" That's where the ROLLUP came in in the GROUP BY and the GROUPING() function in the other spots including that first line in the dynamic SQL.

And, I used REPLACE() twice to simplify the code even more by using replaceable tokens even for the required double-quotes, which make this whole thing a lot more easy to read and troubleshoot in the future. And, finally, notice that we didn't need to use CHAR(13) anywhere because of the way I built the dynamic SQL, which is almost the same as just building normal code.

Here's the final code...

--===== These could be parameters for a stored procedure.
DECLARE  @pStartDate DATETIME   = '10-OCT-2021'
        ,@pEndDate   DATETIME   = '11-OCT-2021' 
        ,@pDebug     BIT        =  1
;
--===== Local Variables
DECLARE  @SQL        NVARCHAR(MAX)
;
--===== Dynamically create the SELECT list according to what's in the table.
 SELECT @SQL = REPLACE(REPLACE(ISNULL(@SQL,N'
 SELECT  [Startdate]   = IIF(GROUPING(CONVERT(CHAR(10),StartDate,23)) = 0,CONVERT(CHAR(10),StartDate,23),"Total")') + N'
        ,[ItemName]    = MAX(IIF(ItemName = "<<ItemName>>",ItemName,""))
        ,[Data1]       = SUM(IIF(ItemName = "<<ItemName>>",Data1   , 0))
        ,[Data2]       = SUM(IIF(ItemName = "<<ItemName>>",Data2   , 0))'
        ,N'<<ItemName>>',ItemName)
        ,N'"',N'''')
   FROM #Table
  WHERE Startdate >= @pStartDate AND Startdate <= @pEndDate
  GROUP BY ItemName 
  ORDER BY ItemName
;
--===== Define the code for the line totals, the "paramtetrized" WHERE clause, the GROUP BY, and the ORDER BY.
 SELECT @SQL += N'
        ,[Total Data1] = SUM(Data1)
        ,[Total Data2] = SUM(Data2)
   FROM #Table
  WHERE Startdate >= @pStartDate AND Startdate <= @pEndDate
  GROUP BY CONVERT(CHAR(10),StartDate,23) WITH ROLLUP
  ORDER BY GROUPING(CONVERT(CHAR(10),StartDate,23)),StartDate
;'
;
--===== If the "Debug" mode is set, display the resulting dynamic SQL
     IF @pDebug <> 0 PRINT @SQL
;
--===== Execute the dynamic SQL to produce the final report.
     -- This is the method to pass the variables into the dynamic SQL instead of "materializing" them
     -- in the dynamic SQL, which would cause a recompile everytime they changed.
   EXEC sp_executesql 
             @SQL                                               --Obviously, this is the dynamic SQL we built
            ,N'@pStartDate DATETIME, @pEndDate DATETIME'        --Defines the variables in the dynamic SQL
            ,@pStartDate = @pStartDate, @pEndDate = @pEndDate   --Assign values to the variables in the dynamic SQL
;

Almost forgot... the GROUPING() function is being used to change the StartDate to "Total" and is also being used in the ORDER BY to make sure that things come out in the order expected. You can read about WITH ROLLUP and the GROUPING() function (which can also be used with WITH CUBE) a the following links.

To be brutally honest, both articles pretty much suck when it comes to examples but they cover the basics.

p.s. IF you're using something less than SQL Server 2012, let me know and we'll change the IIF expressions to CASE expressions (or, you can do it without much angst).

Sameer · January 2, 2022, 4:36pm

Hi Sir JeffModen,
Absolutely amazing outstanding work !
What a great explanation , your code worked like that some one is sitting in a bullet train
one again thank you... You helped me God bless and help you in any aspect of life.

JeffModen · January 2, 2022, 5:06pm

Thank you for the very kind feedback but it was a joint effort. Your description of what you wanted to do was impeccable and you took the time to provide some test data AND it actually followed your graphics. That made this a really good and fun post. I wish more people were like you when they post code related questions.

Happy New Year to you and yours!