SQL Pivot table

Question

Is there a way to pivot an Entity-Attribute table? I want to flip all the rows into columns, regardless of how many different attributes there are.

Here's an example of what I want to accomplish. The example uses two attributes: FirstName, LastName. But in the real database, there are thousands of attributes and I want to flip them into columns for reporting purposes.

I don't want to have to write a CTE for every attribute.

USE TempDB
DECLARE @Attribute TABLE(
AttributeID Int Identity(10,1) PRIMARY KEY,
AttributeName Varchar(MAX))
INSERT INTO @Attribute(AttributeName) VALUES('Firstname')
INSERT INTO @Attribute(AttributeName) VALUES('Lastname')
DECLARE @tbl TABLE(
AttributeID Int,
EntityValue Varchar(MAX)
)
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(10,'John')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(10,'Paul')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(10,'George')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(10,'Ringo')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(11,'Lennon')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(11,'McCartney')
INSERT INTO @tbl(AttributeID,EntityValue) VALUES(11,'Harrison')
SELECT A.AttributeID,AttributeName,EntityValue FROM @tbl T
INNER JOIN @Attribute A
ON T.AttributeID=A.AttributeID


DECLARE @Tbl2 Table(
FirstName Varchar(MAX),
LastName Varchar(MAX)
)
INSERT INTO @Tbl2(FirstName,LastName) VALUES('John','Lennon')
INSERT INTO @Tbl2(FirstName,LastName) VALUES('Paul','McCartney')
INSERT INTO @Tbl2(FirstName,LastName) VALUES('George','Harrison')
INSERT INTO @Tbl2(FirstName) VALUES('Ringo')
SELECT * FROM @Tbl2

Answer 1

Based on what you posted, you're dealing with SQL Server.

The old school method is to use IF or CASE statements to represent each column you want to create. IE:

CASE WHEN t.AttributeID = 10 THEN t.EntityValue ELSE NULL END 'FirstName'

The alternative is to use PIVOT (SQL Server 2005+).

In either case, you're going to have to define the output columns by hand. If you model was setup to address it, you might be able to use dynamic SQL.

Answer 2

In case you're curious, the reason Microsoft SQL Server's PIVOT operator isn't "dynamic," and that you must specify each value to pivot, is that this makes it possible to identify the table structure of the PIVOT query from the query text alone. That's an important principle of most programming languages - it should be possible to determine the type of an expression from the expression. The type shouldn't depend on the run-time values of anything mentioned in the expression.

That said, some implementations of SQL implement what you want. For example, I think Microsoft Access does this with TRANSFORM.

If you search the web for "dynamic pivot", you'll find a lot.