CODEWITHSUNDEEP
t-sqlpivot
Brian Carmicle
Brian Carmicle

Reputation: 95

How can I pivot multiple value columns at once?

I have a table in SQL Server that looks like the following:

| EntityId | AttributeName | ValueSource | ValueText      |
|----------|---------------|-------------|----------------|
| 1        | Name          | Customer    | John Smith     |
| 1        | Age           | Customer    | 25             |
| 1        | Address       | Lookup      | 123 Main St    |
| 2        | Name          | Customer    | Jane Doe       |
| 2        | Age           | Customer    | 30             |
| 2        | Address       | Customer    | 1 Example Road |

Namely, in addition to having the standard EAV columns, it also has a metadata column about the value.

My goal here is to pivot the table into a relational format along the following lines:

| EntityId | Name       | NameSource | Age | AgeSource | Address        | AddressSource |
|----------|------------|------------|-----|-----------|----------------|---------------|
| 1        | John Smith | Customer   | 25  | Customer  | 123 Main St    | Lookup        |
| 2        | JaneDoe    | Customer   | 30  | Customer  | 1 Example Road | Customer      |

However, I'm quite new to T-SQL pivots, and while I can do the standard pivot ignoring ValueSource, I can't quite get both ValueSource and ValueText to pivot into the resulting table. I was hoping 

SELECT EntityId, val.Name, val.Age, val.Address, src.Name AS NameSource, src.Age AS AgeSource, src.Address AS AddressSource
FROM #PivotExample
PIVOT (MAX (ValueText) FOR AttributeName IN (Name, Age, Address)) AS val
PIVOT (MAX (ValueSource) FOR AttributeName IN (Name, Age, Address)) AS src;

but that just tells me that AttributeName is an invalid column name on the second pivot, and I'm not sure where to go from there.

Upvotes: 1

Views: 55

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Here is a working sample of the PIVOT option

Example (or dbFiddle)

Select *
 From  (
        Select A.EntityID
              ,B.*
          From YourTable A
          Cross Apply ( values (AttributeName,ValueText)
                              ,(AttributeName+'Source',ValueSource)
                      ) B(Item,Value)
       ) src
 Pivot (max(Value) for Item in ( [Name],[NameSource],[Age],[AgeSource],[Address],[AddressSource] ) ) pvt

Example

enter image description here

Upvotes: 3

BarneyL
BarneyL

Reputation: 1362

As suggested in the comments, cross tab queries provide a more flexible way of pivoting:

SELECT EntityId
, MAX(IIF(AttributeName = 'Name', ValueText,NULL) AS Name
, MAX(IIF(AttributeName = 'Name', ValueSource,NULL) AS NameSource
, MAX(IIF(AttributeName = 'Age', ValueText,NULL) AS Age
, MAX(IIF(AttributeName = 'Age', ValueSource,NULL) AS AgeSource
, MAX(IIF(AttributeName = 'Address', ValueText,NULL) AS Address
, MAX(IIF(AttributeName = 'Address', ValueSource,NULL) AS AddressSource
FROM #PivotExample
GROUP BY EntityID;

Upvotes: 2

Related Questions