Share via

how to set order by ND columns

RAVI 936 Reputation points
2024-05-08T08:16:54.46+00:00

Hello

This is my sql code

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''
DECLARE @COLS_SUM   NVARCHAR(MAX)=''
DECLARE @COLS_TOT   NVARCHAR(MAX)=''

--Preparing columns for Pivot
SELECT @cols = @cols + isnull(QUOTENAME(ND),0) + ',' 
FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals Horizontal
SELECT @COLS_SUM = @COLS_SUM + isnull(QUOTENAME(ND),0) + '+' 
FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'

--Preparing sum of individual columns for Totals Vertically
SELECT @COLS_TOT = @COLS_TOT +'SUM('+ isnull(QUOTENAME(ND),0) + '),' 
FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp
SELECT @COLS_TOT = SUBSTRING(@COLS_TOT, 0, LEN(@COLS_TOT)) 



SET @query = 
            'SELECT *'+@COLS_SUM+'  INTO #TAB FROM 
            (
                    SELECT CN,isnull(BQ,0)as  BQ,ND
                    FROM temp_days_order_pending
            ) src
            PIVOT 
            (
                    SUM(BQ) FOR ND IN (' + @cols + ')
            ) piv

            SELECT * FROM #TAB
            UNION ALL
            SELECT NULL AS TOTAL ,'+@COLS_TOT+',SUM(TOTAL) FROM #TAB

            '

execute(@query)

how to get order by desc for my ND columns

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,854 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.2K Reputation points MVP
    2024-05-08T21:20:23.58+00:00

    Not sure what exactly code is doing - I have never learnt to use the PIVOT operator. There are better ways to run dynamic pivot, and I have some text about that here: https://www.sommarskog.se/dynamic_sql.html#pivot

    In that section you will also learn how to get sum for columns with GROUPING SETS rather than UNION.

    0 comments
  2. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor
    2024-05-09T01:41:10.82+00:00

    Hi @RAVI

    how to get order by desc for my ND columns

    You can sort the ND columns by adding an ORDER BY when you set the @cols string, like this:

    SELECT @cols = STUFF((SELECT ',' + isnull(QUOTENAME(ND),0) 
                      FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp
                      ORDER BY ND DESC
                      FOR XML PATH('')),1,1,'')

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".