Teja Teja - 1 month ago 4
SQL Question

Performing a custom sort which included order by clause in derived table

I am facing a complex situation where I am aware of the approach which can solve the problem but the order by clause in my derived table is messing up the custom sort. Here are my input and output details and what I have tried.

Schema :-
Input :-

CREATE TABLE Test( Rowname VARCHAR(10), Col1 DATETIME, Col2 DATETIME, Col3 DATETIME, Col4 DATETIME );

INSERT INTO Test VALUES( 'Row1', '2016-01-14', '2016-01-08', '2016-01-30', '2016-01-01' );
INSERT INTO Test VALUES( 'Row2', '2016-01-02', '2016-01-01', '2016-01-18', '2016-01-15' );


Expected Output :-

RowName Result
Row1 Col4,Col2,Col1,Col3
Row2 Col2,Col1,Col4,Col3


What I have tried?

WITH CTE(RowName,Colmn,RN) AS
(
SELECT RowName,Colmn,ROW_NUMBER() OVER( PARTITION BY RowName ORDER BY RowName ) AS RN
FROM
(
( SELECT RowName,Col1 AS Col,'Col1' AS Colmn FROM Test )
UNION ALL
( SELECT RowName,Col2 AS Col,'Col2' AS Colmn FROM Test )
UNION ALL
( SELECT RowName,Col3 AS Col,'Col3' AS Colmn FROM Test )
UNION ALL
( SELECT RowName,Col4 AS Col,'Col4' AS Colmn FROM Test )
) Z
ORDER BY RowName,Col
)
SELECT RowName,
MAX( ( CASE WHEN RN = 1 THEN Colmn END ) ) + ',' +
MAX( ( CASE WHEN RN = 2 THEN Colmn END ) ) + ',' +
MAX( ( CASE WHEN RN = 3 THEN Colmn END ) ) + ',' +
MAX( ( CASE WHEN RN = 4 THEN Colmn END ) ) AS Result
FROM CTE
GROUP BY RowName;


Note :-

The ORDER BY RowName,Col Clause in the inner query/derived table is failing as it is not allowed in SQL Server. If I don't use this ORDER BY then how can I perform custom sort without using ORDER BY clause?

Answer

Your order by should be defined in the over clause:

WITH CTE(RowName,Colmn,RN) AS
(
SELECT 
    RowName,Colmn,ROW_NUMBER() OVER( 
        PARTITION BY RowName 
        ORDER BY RowName, Col       -- add Col here
    ) AS RN 
    FROM
    ( 
      ( SELECT RowName,Col1 AS Col,'Col1' AS Colmn FROM Test )
        UNION ALL
      ( SELECT RowName,Col2 AS Col,'Col2' AS Colmn FROM Test )
        UNION ALL
      ( SELECT RowName,Col3 AS Col,'Col3' AS Colmn FROM Test )
        UNION ALL
      ( SELECT RowName,Col4 AS Col,'Col4' AS Colmn FROM Test )
     )  Z 
    -- ORDER BY RowName,Col -- remove this line
)
SELECT RowName,
       MAX( ( CASE WHEN RN = 1 THEN Colmn END ) )  + ',' +
       MAX( ( CASE WHEN RN = 2 THEN Colmn END ) ) + ',' +
       MAX( ( CASE WHEN RN = 3 THEN Colmn END ) ) + ',' +
       MAX( ( CASE WHEN RN = 4 THEN Colmn END ) ) AS Result
  FROM CTE
GROUP BY RowName;
Comments