logo标签列表

将动态值分成两列并拆分成多列 - 删除重复项。

sqlsql-serverdatabasesql-server-2012
4

我正在努力创建一个查询，可以将列中的多个值拆分成多个列，以帮助“去重”数据集。

如下数据最好解释，但基本上您会注意到一个间隔字段，它是ID、START、FINISH、DURATION、COD列中的密集等级。由于多个重叠的PSSID和CSSID值，这些间隔是重复的。我想知道是否有一种好的方法来动态地将重叠的PSSID和CSSID字段拆分成多个列...！ 那么我实际上是什么意思...

示例数据：

ID  START   FINISH  DURA    COD INT PSSID   CSSID
A1  33.18   33.27   0.09    ST  15  N13045  NULL
A1  33.18   33.27   0.09    ST  15  N13046  NULL
A1  33.27   33.285  0.015   DU  16  N13046  NULL
A1  33.27   33.285  0.015   DU  16  NULL    N20015
A1  33.27   33.285  0.015   DU  16  NULL    N2001516
A1  33.27   33.285  0.015   DU  16  NULL    N20033
A1  33.285  33.35   0.065   BM  17  N13046  NULL
A1  33.285  33.35   0.065   BM  17  NULL    N20015
A1  33.285  33.35   0.065   BM  17  NULL    N2001516
A1  33.285  33.35   0.065   BM  17  NULL    N20033
A1  33.35   33.395  0.045   DM  18  N13046  NULL
A1  33.35   33.395  0.045   DM  18  NULL    N20015
A1  33.35   33.395  0.045   DM  18  NULL    N2001516
A1  33.35   33.395  0.045   DM  18  NULL    N20033
A1  33.395  33.44   0.045   DN  19  N13046  NULL
A1  33.395  33.44   0.045   DN  19  NULL    N20015
A1  33.395  33.44   0.045   DN  19  NULL    N2001516
A1  33.395  33.44   0.045   DN  19  NULL    N20033
A1  33.44   33.485  0.045   BM  20  N13046  NULL
A1  33.44   33.485  0.045   BM  20  NULL    N2001516
A1  33.44   33.485  0.045   BM  20  NULL    N20033
A1  33.44   33.485  0.045   BM  20  NULL    N20034
A1  33.485  33.51   0.025   DN  21  N13046  NULL
A1  33.485  33.51   0.025   DN  21  NULL    N2001516
A1  33.485  33.51   0.025   DN  21  NULL    N20033
A1  33.485  33.51   0.025   DN  21  NULL    N20034
A1  33.51   33.595  0.085   DB  22  N13046  NULL
A1  33.51   33.595  0.085   DB  22  NULL    N2001516
A1  33.51   33.595  0.085   DB  22  NULL    N20034
A1  33.595  33.665  0.07    DN  23  N13046  NULL
A1  33.595  33.665  0.07    DN  23  NULL    N2001516
A1  33.595  33.665  0.07    DN  23  NULL    N20034
A1  33.665  33.785  0.12    DB  24  NULL    N2001516
A1  33.785  33.79   0.005   YS  25  NULL    NULL
A1  33.79   33.83   0.04    BM  26  NULL    NULL

期望输出：

ID  START   FINISH  DURA    COD INT PSSID1  PSSID2  CSSID1  CSSID2      CSSID3
A1  33.18   33.27   0.09    ST  15  N13046  N13045  NULL    NULL        NULL
A1  33.27   33.285  0.015   DU  16  N13046  NULL    N20015  N2001516    N20033
A1  33.285  33.35   0.065   BM  17  N13046  NULL    N20015  N2001516    N20033
A1  33.35   33.395  0.045   DM  18  N13046  NULL    N20015  N2001516    N20033
A1  33.395  33.44   0.045   DN  19  N13046  NULL    N20015  N2001516    N20033
A1  33.44   33.485  0.045   BM  20  N13046  NULL    N20034  N2001516    N20033
A1  33.485  33.51   0.025   DN  21  N13046  NULL    N20034  N2001516    N20033
A1  33.51   33.595  0.085   DB  22  N13046  NULL    N20034  N2001516    NULL
A1  33.595  33.665  0.07    DN  23  N13046  NULL    N20034  N2001516    NULL
A1  33.665  33.785  0.12    DB  24  NULL    NULL    NULL    N2001516    NULL
A1  33.785  33.79   0.005   YS  25  NULL    NULL    NULL    NULL        NULL
A1  33.79   33.83   0.04    BM  26  NULL    NULL    NULL    NULL        NULL

更糟糕的是，这只是一小部分示例数据，对于给定时间间隔可能有超过三个PSSID、CSSID字段（尽管它应该有上限为5）。因此，查询需要是动态的以允许这种情况。

我正在使用SQL Server 2012。上述数据的架构如下所示：

CREATE TABLE #SampleData
    ([ID] varchar(2), [START] decimal(9,2), [FINISH] decimal(9,2), [DURA] decimal(9,2), [COD] varchar(2), [INT] int, [PSSID] varchar(6), [CSSID] varchar(8))
;

INSERT INTO #SampleData
    ([ID], [START], [FINISH], [DURA], [COD], [INT], [PSSID], [CSSID])
VALUES
    ('A1', 33.18, 33.27, 0.09, 'ST', 15, 'N13045', NULL),
    ('A1', 33.18, 33.27, 0.09, 'ST', 15, 'N13046', NULL),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, 'N13046', NULL),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N20015'),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N2001516'),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N20033'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, 'N13046', NULL),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N20015'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N2001516'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N20033'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, 'N13046', NULL),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N20015'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N2001516'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N20033'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, 'N13046', NULL),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N20015'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N2001516'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N20033'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, 'N13046', NULL),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N2001516'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N20033'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N20034'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, 'N13046', NULL),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N2001516'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N20033'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N20034'),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, 'N13046', NULL),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, NULL, 'N2001516'),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, NULL, 'N20034'),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, 'N13046', NULL),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, NULL, 'N2001516'),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, NULL, 'N20034'),
    ('A1', 33.665, 33.785, 0.12, 'DB', 24, NULL, 'N2001516'),
    ('A1', 33.785, 33.79, 0.005, 'YS', 25, NULL, NULL),
    ('A1', 33.79, 33.83, 0.04, 'BM', 26, NULL, NULL)
;

感谢您的帮助！
一旦“枢轴”，保持CSSID和PSSID列的一致性将非常有价值。也就是说，如果一个值出现在一个CSSID列中，那么它将保持在该列中。请参考上面所示的期望输出示例。 - Harry
2个回答
4

您已经定义了创建INT列的组。我们可以使用它，为PSS和CSS分别制作透视表，然后将它们连接起来。

SELECT *
INTO #DataSourcePSS
FROM
(
    SELECT [INT]
          ,[PSSID]
          ,CONCAT('PSSID',ROW_NUMBER() OVER (PARTITION BY [INT] ORDER BY [PSSID] DESC)) AS [RowID]
    FROM #SampleData
) DS
PIVOT
(
    MAX([PSSID]) FOR RowID IN ([PSSID1], [PSSID2], [PSSID3], [PSSID4], [PSSID5])
) PVT

SELECT *
INTO #DataSourceCSS
FROM
(
    SELECT [INT]
          ,[CSSID]
          ,CONCAT('CSSID', ROW_NUMBER() OVER (PARTITION BY [INT] ORDER BY [CSSID] DESC)) AS [RowID] 
    FROM #SampleData
) DS
PIVOT
(
    MAX([CSSID]) FOR RowID IN ([CSSID1], [CSSID2], [CSSID3], [CSSID4], [CSSID5])
) PVT;

WITH DataSourceSD AS 
(
    SELECT DISTINCT [ID], [START], [FINISH], [DURA], [COD], [INT]
    FROM #SampleData
)
SELECT SD.*
      ,PSS.[PSSID1],PSS.[PSSID2],PSS.[PSSID3],PSS.[PSSID4],PSS.[PSSID5]
      ,CSS.[CSSID1],CSS.[CSSID2],CSS.[CSSID3],CSS.[CSSID4],CSS.[CSSID5]
FROM DataSourceSD SD
INNER JOIN #DataSourcePSS PSS
    ON SD.[INT] = PSS.[INT]
INNER JOIN #DataSourceCSS CSS
    ON SD.[INT] = CSS.[INT]
ORDER BY SD.[INT];

DROP TABLE #DataSourceCSS;
DROP TABLE #DataSourcePSS;
DROP TABLE #SampleData;

在此输入图片描述

由于每个组中最多可以有五个值，因此我正在对五个值进行数据透视。在这种情况下，可能会出现某些列没有任何值的情况。如果这不是“OK”，则需要使用动态PIVOT。

1
如果只有一个目标列，使用“PIVOT”非常好。如果有多个目标，则使用“GROUP BY”和聚合更容易进行数据透视。 - Shnugo
1
嗯，人们可以讨论“更容易”的含义... 如果可能的话，我更喜欢使用“即席SQL”。您可以创建一个临时表，在两个步骤中填充它，然后从中创建您的数据透视表。除非您显式地创建它们，否则您的连接和排序将无法使用索引。我的即席SQL完全可以内联到更大的语句中，可以用作“VIEW”或“inline TVF”，而您的多语句方法只能存在于SP中-相当不利... 人们可以考虑使用CTE代替tmp表... 就性能而言，我相信这会相当慢... - Shnugo
1
你可能想要读一下这个链接：http://stackoverflow.com/questions/26936804/pivot-vs-case-t-sql-efficiency，里面有关于 Pivot 和 Case 在 T-SQL 中的效率比较。另外，你还可以在这个链接上找到关于性能方面的更多详细信息：http://www.sqlservercentral.com/articles/T-SQL/63681/。 - Shnugo
非常感谢！已确认可行，但真的希望尝试保持SIDs一致。例如，N20033从CSSID1切换到CSSID2-首选是将其保留在CSSID1中...很抱歉在原始问题中没有清楚说明这一点。 - Harry
2

试试这样

根据您的评论进行更新

现在按照PSSIDs和CSSIDs不同值的内部编号排序。除了您的期望输出外，我得到4个值，因为N20034在新的一列中。我没有看到任何逻辑可以决定一个值应该弹出到哪一列... 这个问题的关键是编号。我的第一个方法根据相对于“parent”的位置对值进行编号，而这个新方法对它们进行排序，以获得每个不同列的唯一值...

WITH Numbered AS
(
    SELECT *
          ,CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END AS ColumnName  
          ,ROW_NUMBER() OVER(PARTITION BY ID,Start,Finish,Dura,COD,[INT],CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END ORDER BY (SELECT NULL)) AS SortNr  
    FROM #SampleData
)
,DistinctPSSIDS AS
(
    SELECT DISTINCT
           DENSE_RANK() OVER(ORDER BY PSSID) AS SortNr
          ,PSSID
    FROM #SampleData
    WHERE PSSID IS NOT NULL
)
,DistinctCSSIDS AS
(
    SELECT DISTINCT
           DENSE_RANK() OVER(ORDER BY CSSID) AS SortNr
          ,CSSID
    FROM #SampleData
    WHERE CSSID IS NOT NULL
)
SELECT ID,Start,Finish,Dura,COD,[INT]
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=1 THEN n.PSSID END) AS PSSID1
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=2 THEN n.PSSID END) AS PSSID2
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=3 THEN n.PSSID END) AS PSSID3
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=4 THEN n.PSSID END) AS PSSID4
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=5 THEN n.PSSID END) AS PSSID5
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=1 THEN n.CSSID END) AS CSSID1
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=2 THEN n.CSSID END) AS CSSID2
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=3 THEN n.CSSID END) AS CSSID3
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=4 THEN n.CSSID END) AS CSSID4
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=5 THEN n.CSSID END) AS CSSID5
FROM Numbered AS n
LEFT JOIN DistinctPSSIDS AS dp ON dp.PSSID=n.PSSID
LEFT JOIN DistinctCSSIDS AS dc ON dc.CSSID=n.CSSID
GROUP BY ID,Start,Finish,Dura,COD,[INT]

结果

+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| ID | Start | Finish | Dura | COD | INT | PSSID1 | PSSID2 | PSSID3 | PSSID4 | PSSID5 | CSSID1 | CSSID2   | CSSID3 | CSSID4 | CSSID5 |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.18 | 33.27  | 0.09 | ST  | 15  | N13045 | N13046 | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.27 | 33.29  | 0.02 | DU  | 16  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.29 | 33.35  | 0.07 | BM  | 17  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.35 | 33.40  | 0.05 | DM  | 18  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.40 | 33.44  | 0.05 | DN  | 19  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.44 | 33.49  | 0.05 | BM  | 20  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033 | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.49 | 33.51  | 0.03 | DN  | 21  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033 | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.51 | 33.60  | 0.09 | DB  | 22  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.60 | 33.67  | 0.07 | DN  | 23  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.67 | 33.79  | 0.12 | DB  | 24  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.79 | 33.79  | 0.01 | YS  | 25  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.79 | 33.83  | 0.04 | BM  | 26  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+

之前：带有GROUP BY和聚合的老式透视表

WITH Numbered AS
(
    SELECT *
          ,CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END AS ColumnName  
          ,ROW_NUMBER() OVER(PARTITION BY ID,Start,Finish,Dura,COD,[INT],CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END ORDER BY (SELECT NULL)) AS SortNr  
    FROM #SampleData
)
SELECT ID,Start,Finish,Dura,COD,[INT]
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=1 THEN PSSID END) AS PSSID1
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=2 THEN PSSID END) AS PSSID2
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=3 THEN PSSID END) AS PSSID3
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=4 THEN PSSID END) AS PSSID4
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=5 THEN PSSID END) AS PSSID5
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=1 THEN CSSID END) AS CSSID1
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=2 THEN CSSID END) AS CSSID2
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=3 THEN CSSID END) AS CSSID3
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=4 THEN CSSID END) AS CSSID4
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=5 THEN CSSID END) AS CSSID5
FROM Numbered
GROUP BY ID,Start,Finish,Dura,COD,[INT]

结果

+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| ID | Start | Finish | Dura | COD | INT | PSSID1 | PSSID2 | PSSID3 | PSSID4 | PSSID5 | CSSID1   | CSSID2   | CSSID3 | CSSID4 | CSSID5 |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.18 | 33.27  | 0.09 | ST  | 15  | N13045 | N13046 | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.27 | 33.29  | 0.02 | DU  | 16  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.29 | 33.35  | 0.07 | BM  | 17  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.35 | 33.40  | 0.05 | DM  | 18  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.40 | 33.44  | 0.05 | DN  | 19  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.44 | 33.49  | 0.05 | BM  | 20  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033   | N20034 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.49 | 33.51  | 0.03 | DN  | 21  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033   | N20034 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.51 | 33.60  | 0.09 | DB  | 22  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20034   | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.60 | 33.67  | 0.07 | DN  | 23  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20034   | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.67 | 33.79  | 0.12 | DB  | 24  | NULL   | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.79 | 33.79  | 0.01 | YS  | 25  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.79 | 33.83  | 0.04 | BM  | 26  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
谢谢您！由于易读性更高，我更喜欢这个答案而不是gotqn的答案。但是仍然存在关于SIDs一致性的问题。您能否想到一种方法，使CSSID / PSSID保持在同一列中（即在您的结果输出中，N2001516从CSSID2变为CSSID1，如果将其保留在一个列中会更好）？ - Harry
@Harry，会有多少不同的值？如果你想在同一列中保持所有相同，可能需要超过5列... - Shnugo
谢谢Shnugo! 看起来很不错。所以根据您的更新，我理解查询在给定ID中限制为5个PSSID。如果有更多，那么查询就需要添加其他列，否则它们将会“丢失”？ - Harry
@Harry，之前没有看到这个问题...是的，这是任何类型的数据透视表都会遇到的一般性问题...有两种解决方法：定义一定数量的列并处理其中大部分为“NULL”，或者将语句动态定义为字符串，并使用“EXEC”执行。在这种情况下，您甚至可以使用您的值作为列名，并只在适用的行中设置“X”。但是这样的结果仅适用于演示...在后续处理中很难使用。知道得越多，走得就越好... - Shnugo
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接