How can one select a static value as a column based when using UNPIVOT?

Question

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.

Answer 1

You are missing a new column generated by unpivoting attributes

SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue]
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT 
(
  Value FOR attributes IN ( type, code, description)
) AS myValues