set order by based on asc series

RAVI 916 Reputation points
2024-05-15T17:50:11.12+00:00

Hello

This is my sql table with data

 
/****** Object:  Table [dbo].[temp_days_order_pending]    Script Date: 05/15/2024 23:12:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[temp_days_order_pending](
	[CN] [varchar](400) NULL,
	[BQ] [float] NULL,
	[ND] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'A', 1000, N'4')
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'KK', 1100, N'23')
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'M', 400, N'11')
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'NN', 200, N'2')
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'KL', 30, N'1')
INSERT [dbo].[temp_days_order_pending] ([CN], [BQ], [ND]) VALUES (N'R', 50, N'14')

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 = STUFF((SELECT ',' + isnull(QUOTENAME(ND),0)     
                      FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp    
                      ORDER BY ND asc    
                      FOR XML PATH('')),1,1,'')    
    
        
--Preparing sum of columns for Totals Horizontal        
         
        
SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + 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) 

it shows like this output

123

but i want like this below order by 1,2,4,11,14,23

123

how to do so like what to change in my sql code to get this in asc format

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,298 questions
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,852 questions
{count} votes

Accepted answer
  1. Gowtham CP 1,005 Reputation points
    2024-05-15T18:23:28.7566667+00:00

    Hello RAVI ,

    I've achieved the desired order by dynamically constructing a CASE statement within the ORDER BY clause. Additionally, by including CAST(ND AS INT), the values of ND are treated as integers for proper sorting, ensuring that the final result set is ordered correctly.

    DECLARE @cols NVARCHAR(MAX)=''
    DECLARE @query NVARCHAR(MAX)=''
    DECLARE @COLS_SUM NVARCHAR(MAX)=''
    DECLARE @COLS_TOT NVARCHAR(MAX)=''
    
    -- Preparing columns for Pivot
    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 CAST(ND AS INT) ASC
                          FOR XML PATH('')), 1, 1, '')
    
    -- Preparing sum of columns for Totals Horizontal
    SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + 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 with dynamic order based on a variable
    DECLARE @desired_order NVARCHAR(MAX) = '1, 2, 4, 11, 14, 23' -- defining the order
    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
        ORDER BY ' +
        (
            SELECT STUFF(
            (
                SELECT ', CASE WHEN ' + QUOTENAME(ND) + ' IS NOT NULL THEN ' + QUOTENAME(ND) + ' END'
                FROM temp_days_order_pending
                ORDER BY CAST(ND AS INT)
                FOR XML PATH('')
            ), 1, 2, ''
            )
        ) -- dynamic order variable
    EXECUTE(@query)
    
    

    Don't forget to upvote! If you found this solution helpful, consider accepting it.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Viorel 112.8K Reputation points
    2024-05-15T18:04:14.5766667+00:00

    Try to replace ORDER BY ND with ORDER BY cast(ND as int).

    Also calculate the @COLS_TOT in this manner:

    SET @COLS_TOT = STUFF((SELECT ', sum(' + isnull(QUOTENAME(ND),0) + ')'     
                          FROM (SELECT DISTINCT ISNULL(ND,0) as ND FROM temp_days_order_pending ) AS tmp    
                          ORDER BY cast(ND as int) asc    
                          FOR XML PATH('')),1,1,'')    
    
    0 comments No comments

  2. Bruce (SqlWork.com) 57,641 Reputation points
    2024-05-15T20:47:29.77+00:00

    never mind

    0 comments No comments

  3. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor
    2024-05-16T01:43:29.9633333+00:00

    Hi @RAVI

    The idea of solving the problem is the same as I answered in your previous post, but at that time we didn't know that the data type of the ND column was varchar, so we didn't get the expected sorting result.

    Have you tried convert the datatype to INT first to order correctly.

    After modify ORDER BY ND DESC to ORDER BY CAST(ND AS INT) ASC, I got the desired result.

    Check this:

    --Preparing columns for Pivot   
        
    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 CAST(ND AS INT) asc    
                          FOR XML PATH('')),1,1,'')    
    PRINT @cols  
    

    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".

    0 comments No comments