Sql-server – How to one select a static value as a column based when using UNPIVOT

reportingsql servert-sqlunpivot

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.

Best Answer

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

Related Solutions

Sql-server – TSQL Query – Efficient

In my personal opinion, it seems as all of those queries have very separate requirements and output. I would personally keep them separate to the end. In other words, what are you going to gain out of filling a temporary object with a key/value pair of the output of 5 unrelated queries?

I'd wrap them each in a stored procedure, parameterize what I can to make code reusable, and then just make individual calls to each whenever I needed to. I like to follow the principle that a logical piece of code (stored proc, method, etc.) should do just one thing.

So the approach I would take is something like this:

create proc dbo.GetDetail
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Detail')
    ORDER BY Quantity DESC;
go

create proc dbo.GetCurrent
    @Quantity int = 0
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Current')
    AND Quantity >= @Quantity
    ORDER BY Quantity DESC;
go

So on and so forth...

That way when you needed the data, you'd make your calls:

exec dbo.GetDetail;
exec dbo.GetCurrent;
exec dbo.GetCurrent @Quantity = 10;

Why this particular approach? Because I think this scales well. It's the way I typically approach database development. I'm completely fine with having a bunch of small pieces of executable code. It beats having a few big ones, and having to modify accordingly. That doesn't grow.

This is my opinion, and I'm looking forward to seeing what others would do in this scenario.

Sql-server – Why Does UNPIVOT Work on Compatibility Level 80 DB

The answer is in the error message:

You may need to set the compatibility level of the current database to a higher value to enable this feature.

Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces the error message:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

The following executes successfully (assuming tempdb has a compatibility level > 80):

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

You need to check what the context database is for the connection that produces the error.

Best Answer

Related Solutions

Sql-server – TSQL Query – Efficient

Sql-server – Why Does UNPIVOT Work on Compatibility Level 80 DB

Related Question