Help with pivot table query with a where clause

Question

Help with pivot table query with a where clause

system243trd

Right there with Babe

Points: 758
More actions
June 28, 2024 at 3:07 pm

#4432183

I have a table which contains the ID, AVALUE, TYPE,NUMBER1 and NUMBER2
TYPE is either A or B
I want to be able to pivot the data as table below if the type is A then insert the data into ANumber 1 or ANumber2.
The result should ensure that the IS column is unique.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3669 More actions · Answer 1

You should be able to use MAX CASE() to pivot the columns assuming there are no duplicates of ID and TYPE.

What do you want to see for missing values? This will return zeros. If you have values = zero and need to differentiate between zero and missing, then ELSE NULL or ELSE '' might work better. Assuming the columns are numeric there will be implicit conversions with ELSE '' and it might return zero unless you specifically cast it to varchar.

SELECT  ID, AVALUE, 
        MAX(CASE WHEN [TYPE] = 'A' THEN NUMBER1 ELSE 0 END) AS ANUMBER1,
        MAX(CASE WHEN [TYPE] = 'A' THEN NUMBER2 ELSE 0 END) AS ANUMBER2,
        MAX(CASE WHEN [TYPE] = 'B' THEN NUMBER1 ELSE 0 END) AS BNUMBER1,
        MAX(CASE WHEN [TYPE] = 'B' THEN NUMBER2 ELSE 0 END) AS BNUMBER2
FROM    dbo.YourTable
GROUP   BY ID, AVALUE

ScottPletcher SSC Guru Points: 101123 More actions · Answer 2

Assuming that AVALUE is based on the ID value (that is, the same ID always has the same AVALUE), then you can leave AVALUE out of the GROUP BY. If the table is clustered on ID, that will save you a sort, which is a fairly expensive operation. If, and only if, the table is clustered first on AVALUE, then reverse, and GROUP BY AVALUE and use MAX(ID).

SELECT  ID, 
        MAX(AVALUE) AS AVALUE, --<<--
        MAX(CASE WHEN [TYPE] = 'A' THEN NUMBER1 ELSE 0 END) AS ANUMBER1,
        MAX(CASE WHEN [TYPE] = 'A' THEN NUMBER2 ELSE 0 END) AS ANUMBER2,
        MAX(CASE WHEN [TYPE] = 'B' THEN NUMBER1 ELSE 0 END) AS BNUMBER1,
        MAX(CASE WHEN [TYPE] = 'B' THEN NUMBER2 ELSE 0 END) AS BNUMBER2
FROM    dbo.YourTable
GROUP   BY ID --<<--

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".