name/value attribute query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

name/value attribute query

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-01-07 : 13:13:55

Im working with a table

member_id INT
attribute_id INT
attribute_value VARCHAR(100)

For example, attribute_id = 1 is a first_name, attribute_id = 2 is a last_name.

I can perform the following in two queries, but can I accomplish the same in a single select? Case somehow...?? Thanks guys!

SELECT @first_name = attribute_value
FROM table_attribute
WHERE attribute_id = 1

SELECT @last_name = attribute_value
FROM table_attribute
WHERE attribute_id = 2

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-01-07 : 13:17:25

Of course, that would have been...

SELECT @first_name = attribute_value
FROM table_attribute
WHERE member_id = 1 AND attribute_id = 1

...etc.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-01-07 : 13:26:37

one way...

SELECT @first_name = ta1.attribute_value,
@last_name = ta2.attribute_value
FROM table_attribute ta1 INNER JOIN table_attribute ta2
ON ta1.member_id = ta2.member_id
WHERE ta1.member_id = 1 AND
ta1.attribute_id = 1 AND
ta2.attribute_id = 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources