I'm trying to unpivot a few columns in a table. This is for a reporting tool. The source data is already normalized. I am able to get my desired results with a series of UNION ALL statements. I can get close to what I want with UNPIVOT, but I'm missing one part. I'd like to get the source column name in my UNPIVOT results.
Table structure: | itemNumber | type | code | description | | BN3466774 | AUD1 | 444 | desc--text | | 1238udd74 | AUD1 | 331 | fdsafdsafdsa | | AA34fg774 | MAN3 | 874 | asdfasdfasdf | Desired Output: | itemNumber | SourceName | SourceValue | | BN3466774 | type | AUD1 | | BN3466774 | code | 444 | | BN3466774 | description| desc--text | | 1238udd74 | type | AUD1 | | 1238udd74 | code | 331 | | 1238udd74 | description| fdsafdsafdsa| ... you get the idea
As mentioned before, I can get this result using a series of UNION ALL statements
SELECT * FROM ( SELECT itemNumber, 'type' as SourceName, type as SourceValue FROM myTable UNION ALL SELECT itemNumber, 'code ' as SourceName, code as SourceValue FROM myTable UNION ALL SELECT itemNumber, 'description ' as SourceName, description as SourceValue FROM myTable ) as myValues
My query using UNPIVOT looks like this:
SELECT itemNumber, Value FROM ( SELECT itemNumber, type, code, description FROM myTable ) AS tb UNPIVOT ( Value FOR attributes IN ( type, code, description) ) AS myValues
This query results in the following data:
Desired Output: | itemNumber | SourceValue | | BN3466774 | AUD1 | | BN3466774 | 444 | | BN3466774 | desc--text | | 1238udd74 | AUD1 | | 1238udd74 | 331 | | 1238udd74 | fdsafdsafdsa|
The reason I'd like to go with the UNPIVOT is because it tends to execute about 50% faster than the UNION ALL approach, and in one query I've got about 16m rows being returned.
I'd like to get the source column into my UNPIVOT results, but I've been unable to generate a query that does this.