CODEWITHSUNDEEP
sqlt-sql
Jason Lee
Jason Lee

Reputation: 29

PIVOT without aggregation function

I have a single table, and I want to pivot it to new table. I used pivot to implements but the aggregate function will filter data. How to pivot table without aggregate function, or could u can give me recommendation for this question.

Orginal Table

ID    Name    Value    Date
1     A       5.00     06/01/2019 13:00
2     A       13.15    06/02/2019 15:32
3     B       3.20     06/02/2019 15.32
4     B       33.11    05/11/2019 13:00
5     B       32.00    05/11/2019 13:00

trans to new table

ID    A     B     Date
1     5.00  NULL  06/01/2019 13:00
2     13.15 3.20  06/02/2019 15:32
3     NULL  33.11 05/11/2019 13:00
4     Null  32.00 05/11/2019 13:00

notes: ID is identity on two table.

my pivot code, it only keep max value.

PIVOT(
    MAX(Value) 
    FOR Name IN (A,B)) AS S
ORDER BY Date DESC

Upvotes: 0

Views: 88

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521429

A standard pivot query should work here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Date, Name ORDER BY ID) rn
    FROM yourTable
)

SELECT
    Date,
    MAX(CASE WHEN Name = 'A' THEN [Value] END) AS A,
    MAX(CASE WHEN Name = 'B' THEN [Value] END) AS B
FROM cte
GROUP BY
    Date, rn;

Demo

Upvotes: 2

Related Questions