Pivot-Like Query for Christmas?

SQL Server 2000. I have the following table and need to turn it into
something "flatter". Gurus, can you help me come up with any SQL
2000-compliant TSQL that can make this happen?

I have this:
CREATE TABLE #Pairs
(
RowId Integer,
ItemKey VARCHAR(50),
ItemValue VARCHAR(50),
ItemType VARCHAR(50),
ItemDesc  VARCHAR(50)
);
INSERT INTO #Pairs VALUES(1,'Height','84','Int','Height of the Door');
INSERT INTO #Pairs VALUES(2,'Width','40','Int','Widtrh of the door.');
INSERT INTO #Pairs VALUES(3,'Thickness','2','Int','Thickness of the
door.');
INSERT INTO #Pairs VALUES(4,'Color','White','VarChar(50)','Color of
the door.');

But I need to turn it into a table that looks like this:

RowId   Height   Width  Thickness  Color
1       84       null   null       null
2       null     40     null       null
3       null     null   2          null
4       null     null   null       White


Thanks and Merry Christmas!

EAV is not the best database design, but here is a query that should

EAV is not the best database design, but here is a query that should generate
the results you want.

SELECT RowId,
CASE WHEN ItemKey = 'Height' THEN ItemValue END AS Height,
CASE WHEN ItemKey = 'Width' THEN ItemValue END AS Width,
CASE WHEN ItemKey = 'Thickness' THEN ItemValue END AS Thickness,
CASE WHEN ItemKey = 'Color' THEN ItemValue END AS Color
FROM #Pairs;

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

Thanks for the fast response.

Thanks for the fast response. My sample has just a few ItemKey but my
actual stuff has 60 and can grow to an unknown length. Do I have any
other option besides CASE WHEN?

Thanks.

Google for EAV or "Entity Attribute Value" or insanely bad schemadesigns to

Google for EAV or "Entity Attribute Value" or insanely bad schema
designs to find find a few thousand words and examples of why we do not
do this in good SQL.

SnapDive (SnapDive@community.

SnapDive (SnapDive@community.nospam) writes:

In that case you need to build the query dynamically. First read the
actual ItemKey values, and then build the query from there. There is
nothing built in. A query in SQL returns a table, and a table has a
well-defined set of columns.

You may also be interested in exploring RAC, a third-party product
which helps you this tedious work, http://www.rac4sql.net.

As for the EAV design, as pointed out by Michael Coles and Joe Celko,
it is a design that comes with a whole bunch of problems, but there are
places where it is the best chioce. Whether yours is such a case, we do not
know.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx