CODEWITHSUNDEEP
sqlsql-servert-sql
Faiz
Faiz

Reputation: 283

pivot table returns more than 1 row for the same ID

I have a sql code which I am using to do pivot. Code is as follows:

SELECT DISTINCT PersonID
,MAX(pivotColumn1)
,MAX(pivotColumn2) --originally these were in 2 separate rows)
FROM(SELECT srcID, PersonID, detailCode, detailValue) FROM src) AS SrcTbl
PIVOT(MAX(detailValue) FOR detailCode IN ([pivotColumn1],[pivotColumn2])) pvt
GROUP BY PersonID

In the source data the ID has 2 separate rows due to having its own ID which separates the values. I have now pivoted it and its still giving me 2 separate rows for the ID even though i grouped it and used aggregation on the pivot columns. Ay idea whats wrong with the code?

So I have all my possible detailCode listed in the IN clause. So I have null returned when the value is none but I want it all summarised in 1 row. See image below.

enter image description here

Upvotes: 1

Views: 1695

Answers (1)

sagi
sagi

Reputation: 40481

If those are all the options of detailCode , you can use conditional aggregation with CASE EXPRESSION instead of Pivot:

SELECT t.personID,
       MAX(CASE WHEN t.detailCode = 'cas' then t.detailValue END) as cas,
       MAX(CASE WHEN t.detailCode = 'buy' then t.detailValue END) as buy,
       MAX(CASE WHEN t.detailCode = 'sel' then t.detailValue END) as sel,
       MAX(CASE WHEN t.detailCode = 'pla' then t.detailValue END) as pla
FROM YourTable t
GROUP BY t.personID

Upvotes: 2

Related Questions