Блог

Я сталкиваюсь со сложной ситуацией, когда я знаю о подходе, который может решить проблему, но предложение order by в моей производной таблице портит пользовательскую сортировку. Вот мои входные и выходные данные и то, что я пробовал.

Схема:-
Ввод:-

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

Ожидаемый результат :-

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

Что я пробовал?

 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;
  

Примечание :-

Предложение ORDER BY RowName,Col во внутреннем запросе / производной таблице не выполняется, поскольку это не разрешено в SQL Server. Если я не буду использовать этот порядок к тому времени, как я могу выполнить пользовательскую сортировку без использования предложения ORDER BY?

Ваш order by должен быть определен в over предложении:

 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;
  

если вы добавите col into к order by в row_number, он будет упорядочен так, как вы хотите.

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

 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