set order by based on asc series

Question

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

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

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

Answer 1

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.

Answer 2

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,'')    

Answer 3

never mind

Answer 4

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