CODEWITHSUNDEEP
sqlselectuniontransposeunpivot
Flib
Flib

Reputation: 175

SQL multiples unpivot

I'm having some difficult with multiples unpivot and also tried APPLY. So Lets say I have:

[name]   [Type1name]  [Type2name]  [Region1] [region2]  [Type1valu] [type2value] 
abc       car          bike            US        JP       500         300
def       car          boat            US        UK       150         200
ghi       boat         *null*          BR        *null*    50          *null*

now I want to aggregate [Type1name] + [Type2name] and [Region1] + [Region2] on same columns and values in each respective line:

[name]  [Typename]  [Region] [Value]
abc     car            US     500
abc     bike           JP     300
def     car            US     150  
def     boat           UK     200
ghi     boat           BR     50

Upvotes: 1

Views: 114

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT A.name, A.Type1name AS Typename, A.Region1 AS Region, A.Type1value AS 'Value'
FROM tableA A 
WHERE A.Type1name IS NOT NULL
UNION ALL 
SELECT A.name, A.Type2name AS Typename, A.Region2 AS Region, A.Type2value AS 'Value'
FROM tableA A
WHERE A.Type2name IS NOT NULL;

Upvotes: 1

Related Questions