SQL Server Alternate way to pivot the data without using SUM or MAX?

o75abkj4 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(85)

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

sql-server

来源：https://stackoverflow.com/questions/76706617/alternate-way-to-pivot-the-data-without-using-sum-or-max

1条答案

按热度按时间

qqrboqgw1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Alternate way to pivot the data without using SUM or MAX?

1条答案

相关问题

热门标签

最新问答