编号重复的行组-数字组
我有一个表格，其中的一栏显示重复的数字1至7。
EDRImportID FileID value colnum rn
2 752454 PN 1 1
2 752454 NNN 2 2
2 752454 NNNN 3 3
2 752454 01/01/2019 00:00 4 4
2 752454 0 5 5
2 752454 01/01/2019 00:30 6 6
2 752454 0 7 7
2 752454 NNN 1 8
2 752454 NNNN 2 9
2 752454 SHOS-1 3 10
2 752454 01/01/2019 00:30 4 11
2 752454 0 5 12
2 752454 01/01/2019 01:00 6 13
2 752454 0 7 14
2 752454 NNN 1 15
2 752454 NNNN 2 16
所以我想第1行7到GroupNumber 1，第8到14行GroupNumber 2等。
该表来自保存在表中的csv字符串，并将始终具有7行。这是生成表的SQL。
SELECT
I.EDRImportID,
FileID,
value,
ROW_NUMBER() OVER(PARTITION BY Record,FileID ORDER BY (SELECT NULL)) as colnum,
ROW_NUMBER() OVER(PARTITION BY FileID ORDER BY (SELECT NULL)) as rn
FROM Base.Genesis_ImportFile I
CROSS APPLY STRING_SPLIT(Record, ',') AS Cols
WHERE I.FileType = 'PN'
AND I.Record like 'PN%'
AND I.EDRImportID = 2
AND FileID = 752454
一旦有了组号，就需要将每个组的7行透视为7列。我尝试了下面的代码，但是它使用了MAX聚合，该聚合将仅返回7列的一行，而不是几行
WITH PNColumns AS
(
SELECT
I.EDRImportID,
FileID,
value,
ROW_NUMBER() OVER(PARTITION BY Record,FileID ORDER BY (SELECT NULL)) as rn
FROM Base.Genesis_ImportFile I
CROSS APPLY STRING_SPLIT(Record, ',') AS Cols
WHERE I.FileType = 'PN'
AND I.Record like 'PN%'
AND I.EDRImportID = 2
AND FileID = 752454
)
SELECT
EDRImportID,
FileID,
[2] AS Station,
[3] AS Unit,
[4] AS FromDate,
[5] AS FromMW,
[6] AS ToDate,
[7] AS ToMW
FROM PNColumns
PIVOT(
MAX(value)
FOR rn IN ([2],[3],[4],[5],[6],[7])
) as PVT
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
根据你的需求，你希望在已有分组逻辑的基础上，将每个组的7行数据透视为7列。但是目前的尝试中使用了MAX聚合函数导致只得到了单行结果。为了保留多行数据并进行透视，你可以考虑不使用聚合函数，而是直接通过条件筛选来构造透视表。然而，标准的SQL PIVOT操作确实需要聚合函数，因此我们可以采用CASE语句结合GROUP BY来模拟这种透视效果。
下面是一个可能的解决方案：
WITH PNGrouped AS (
SELECT
EDRImportID,
FileID,
value,
ROW_NUMBER() OVER(PARTITION BY Record,FileID ORDER BY (SELECT NULL)) as colnum,
-- 添加一个组号标识，每7行一个新组
(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1) / 7 + 1 AS GroupNumber
FROM Base.Genesis_ImportFile I
CROSS APPLY STRING_SPLIT(I.Record, ',') AS Cols
WHERE I.FileType = 'PN'
AND I.Record LIKE 'PN%'
AND I.EDRImportID = 2
AND FileID = 752454
)
SELECT
EDRImportID,
FileID,
MAX(CASE WHEN colnum = 1 THEN value END) AS Station,
MAX(CASE WHEN colnum = 2 THEN value END) AS Unit,
MAX(CASE WHEN colnum = 4 THEN value END) AS FromDate,
MAX(CASE WHEN colnum = 5 THEN value END) AS FromMW,
MAX(CASE WHEN colnum = 6 THEN value END) AS ToDate,
MAX(CASE WHEN colnum = 7 THEN value END) AS ToMW
FROM PNGrouped
-- 根据组号和colnum进行分组，确保每一组的数据被正确处理
GROUP BY EDRImportID, FileID, GroupNumber
ORDER BY GroupNumber;
这个查询首先在CTE PNGrouped 中计算出每行数据属于哪个组（假设每7行为一组），然后在主查询中利用CASE语句和GROUP BY来实现透视效果，同时保留了原始数据中的多行结构，每7行数据转换为一行输出，且没有丢失信息。注意这里我调整了colnum的引用以匹配你提供的列序号，特别是忽略了原问题中未提及用途的第3列（NNNN）以及重复的第7列（0）。请根据实际情况调整CASE语句中的条件和列顺序。
