主Sql没有聚合

Question

我知道我不能在没有聚合max() &min () .

我正试图找出一个变通方法，但类似问题的答案在我的脑海中航行。有人有解决这个问题的建议吗?

数据表:

试试这个

Select
pax,
concat(mis, '-', rn) as mis_new,
codex
from (
Select
pax,
mis,
row_number() over (partition by pax, mis 
order by mis ) rn 
from table
) t
pivot (
max(codex) for (
mis_new in ('10-1','10-2', '5-1','7-1','7-2','8-1','9-1','10-3')
) pvt

看起来对行号的标准条件聚合会更好:

DECLARE @cols  AS NVARCHAR(MAX) =
(
SELECT CONCAT(',MIN(CASE WHEN mis = ', QUOTENAME(mis, ''''), ' AND rn = ', rn, ' THEN codex END) ', QUOTENAME(mis))
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY pax ORDER BY mis) rn
FROM dd
) as tmp
GROUP BY mis, rn
FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)');
DECLARE @query AS NVARCHAR(MAX) = '
SELECT
pax' + @cols + '
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY pax ORDER BY mis) rn
FROM dd
) dd
GROUP BY pax;
';
PRINT @query; --for testing
EXEC sp_executesql
@query;

,db&lt的在小提琴

注意使用FOR XML进行聚合。由于不可预测性，不应使用可变合并。