bob
3/23/2007 11:12:00 AM
A few methods:
DROP TABLE #eav
GO
CREATE TABLE #eav ( id CHAR(2) NOT NULL, attribute_name VARCHAR(20) NOT
NULL, string_value VARCHAR(50), date_value DATETIME, numeric_value INT,
PRIMARY KEY ( id, attribute_name ) )
GO
INSERT INTO #eav ( id, attribute_name, string_value ) VALUES ( '01', 'Type',
'ABC' )
INSERT INTO #eav ( id, attribute_name, date_value ) VALUES ( '01', 'DOB',
'20070101' )
INSERT INTO #eav ( id, attribute_name, numeric_value ) VALUES ( '01',
'Count', '100' )
INSERT INTO #eav ( id, attribute_name, string_value ) VALUES ( '02', 'Type',
'DEF' )
INSERT INTO #eav ( id, attribute_name, date_value ) VALUES ( '02', 'DOB',
'20070102' )
INSERT INTO #eav ( id, attribute_name, numeric_value ) VALUES ( '02',
'Count', '200' )
INSERT INTO #eav ( id, attribute_name, string_value ) VALUES ( '03', 'Type',
'GHI' )
INSERT INTO #eav ( id, attribute_name, date_value ) VALUES ( '03', 'DOB',
'20070103' )
INSERT INTO #eav ( id, attribute_name, numeric_value ) VALUES ( '03',
'Count', '400' )
GO
-- One solution, plus suitable for SQL 2000
SELECT
id,
MAX( CASE WHEN attribute_name = 'Type' THEN string_value END ),
MAX( CASE WHEN attribute_name = 'DOB' THEN date_value END ),
MAX( CASE WHEN attribute_name = 'Count' THEN numeric_value END )
FROM #eav
GROUP BY id
GO
-- Or table for each attribute ( also SQL 2000 )
SELECT
a.id,
a.string_value AS 'Type',
b.date_value AS 'DOB',
c.numeric_value AS 'Count'
FROM #eav a
LEFT JOIN #eav b ON a.id = b.id AND b.attribute_name = 'DOB'
LEFT JOIN #eav c ON a.id = c.id AND c.attribute_name = 'Count'
WHERE a.attribute_name = 'Type'
GO
-- Get everything into one column, then pivot it - SQL 2005 method
SELECT *
FROM
(
SELECT
a.id,
attribute_name,
COALESCE ( string_value, CAST( date_value AS VARCHAR ), CAST(
numeric_value AS VARCHAR ) ) AS column_data
FROM #eav a
) x
PIVOT ( MAX( column_data ) FOR attribute_name In ( [Type], [DOB], [Count] )
) pvt
Let me know how you get on.
wBob
"S Chapman" wrote:
>
> I have a table that stores data as EAV as follows:
>
> --------------------------------------------------------------------
> ID | Name | Str Value| Date Value| Num Value
> --------------------------------------------------------------------
> 01 | Type | ABC | |
> 01 | Count | | | 100
> 01 | DOB | | 01/01/07 |
>
> 02 | Type | DEF | |
> 02 | Count | | | 200
> 02 | DOB | | 02/01/07 |
>
> 03 | Type | GHI | |
> 03 | Count | | | 400
> 03 | DOB | | 03/01/07 |
>
> I am trying to extract the results in the following form:
>
> ID, Name, DOB, Count
> ----------------------------------------
> 01,ABC, 01/01/07,100
> 02,DEF, 02/01/07,200
> 03,GHI, 03/01/07,300
>
> I can't seem to work out an elegant way of extrating the results using
> Sql. The columns ID, Name, DOB and Count are userdefined which makes
> it more complicated.
>
> Any help would be greatly appreciated.
>
> Thanks.
>
>