Pivot-Like Query for Christmas?

SnapDive

unread,

Dec 25, 2009, 8:58:31 PM12/25/09

to

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!

Michael Coles

unread,

Dec 25, 2009, 9:17:27 PM12/25/09

to

EAV isn't the best database design, but here's 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)
----------------

"SnapDive" <Snap...@community.nospam> wrote in message
news:s9raj55gjer3kmfig...@4ax.com...

SnapDive

unread,

Dec 25, 2009, 9:35:54 PM12/25/09

to

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.

--CELKO--

unread,

Dec 25, 2009, 11:42:42 PM12/25/09

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 don't
do this in good SQL.

Erland Sommarskog

unread,

Dec 26, 2009, 3:53:04 AM12/26/09

to

SnapDive (Snap...@community.nospam) writes:
> 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?

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 don't
know.

--
Erland Sommarskog, SQL Server MVP, esq...@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