英文:
Alternate way to pivot the data without using SUM or MAX?
问题
我需要转置数据，但当我使用 PIVOT SUM / MAX 进行转置时，它会对值进行聚合。如何在不进行聚合的情况下获取数据？
需要帮助解决这个 SQL 查询以获得输出。
表中的数据:
Code ColVal
A 1
A 2
A 3
B 1
B 2
B 3
B 4
C 1
C 2
期望的输出:
A B C
1 1 1
2 2 2
3 3 NULL
NULL 4 NULL
查询:
SELECT A, B, C
FROM (
SELECT Code, ColVal
FROM Table) TBL
PIVOT (SUM(ColVal) FOR Code IN (A, B, C)) AS pvt
英文:
I need to transpose a data but when I do so using PIVOT SUM / MAX is aggregating the value. How to get the data as without aggregating?
Need a help on this SQL query to get output.
Data in table:
Code ColVal
A 1
A 2
A 3
B 1
B 2
B 3
B 4
C 1
C 2
Expected output:
A B C
1 1 1
2 2 2
3 3 NULL
NULL 4 NULL
Query:
SELECT A, B, C
FROM (
SELECT Code,ColA
FROM Table) TBL
PIVOT (SUM(ColA) FOR Code IN (A,B,C)) AS pvt
答案1
得分: 3
PIVOT需要一个聚合函数。
要获得您想要的结果，您需要为隐式的GROUP BY添加一个合成列。 示例。
每个RN, Code组合只会有一行，所以MAX只是对单个值进行聚合
从 (
选择 Code，/*扩展列*/
ColVal，/*值列*/
ROW_NUMBER() OVER (PARTITION BY Code ORDER BY ColVal) AS RN /*分组列*/
从 YourTable) TBL
PIVOT (MAX(ColVal) FOR Code IN (A,B,C)) AS pvt
英文:
PIVOT requires an aggregate.
To get your desired results you need to add a synthetic column for the implicit GROUP BY. Fiddle.
There will only be one row per RN, Code combination so the MAX just aggregates that single value
SELECT A, B, C
FROM (
SELECT Code, /* Spreading column */
ColVal, /* value column */
ROW_NUMBER() OVER (PARTITION BY Code ORDER BY ColVal) AS RN /* grouping column */
FROM YourTable) TBL
PIVOT (MAX(ColVal) FOR Code IN (A,B,C)) AS pvt
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论