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

Question

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

Answer 1

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