SQL Server - Pivot-Like Query for Christmas?

Asked By SnapDive

25-Dec-09 08:58 PM

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!

25-Dec-09 09:17 PM

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)
----------------

25-Dec-09 09:35 PM

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.

26-Dec-09 12:54 PM

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.

26-Dec-09 12:54 PM

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