#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
Я сталкиваюсь со сложной ситуацией, когда я знаю о подходе, который может решить проблему, но предложение 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?
Ответ №1:
Ваш 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;
Ответ №2:
если вы добавите col into к order by в row_number, он будет упорядочен так, как вы хотите.
ROW_NUMBER() OVER( PARTITION BY RowName ORDER BY RowName, col )
Ответ №3:
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