CODEWITHSUNDEEP
sqlsql-servert-sql
Teja
Teja

Reputation: 13534

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?

Upvotes: 1

Views: 900

Answers (3)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

declare  @test TABLE( 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' );



;with cte as(
    select a.Rowname, 'Col' + b.ID as ColName, b.Col from @test as a
    outer apply (select * from (values ('1', Col1), ('2', Col2), ('3', Col3), ('4', Col4)) as t(ID, Col)) as b
)
    select Distinct Rowname,
          (Select ColName + ', ' From cte as b 
          Where b.Rowname=a.Rowname 
          order by b.Col for xml path(''), type).value('.', 'varchar(30)') as ColList
    from cte as a

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

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;

Upvotes: 1

ZedZim
ZedZim

Reputation: 53

if you add col into to the order by in the row_number it will order the way you want.

ROW_NUMBER() OVER( PARTITION BY RowName ORDER BY RowName, col )

Upvotes: 0

Related Questions