sql select multiple rows into one column

I'm sorry, i don't know how this is called so i've also no idea how to google it. I'm going to try and explain what i need. There's a standard scenario where i have some sort of object-storing table and there's another table, containing object attributes. One object may be associated with many attributes. So basically when you need all data about an object, you'll join the attributes table and receive lots of rows. In one scenarion, i'm que开发者_Python百科rying lots of objects at the same time and doing a subselect to select one attribute:

SELECT basic data,
(SELECT some attribute from attributes table) AS attribute1
FROM objects table
JOIN something
JOIN something
...

Everything is OK, but i need to select a few more attributes from that list. What would be an optimal way to do so? I think writing 2 more subqueries is not a very good idea. I've heard about some pivot functions in t-sql, but i'm not sure if that's what i need. Ideally attributes would be returned as new columns in a single row with all the other data, attribute name being the new column name and attribute text being the new column value. But attribute names may contain spaces etc. so i'm not sure if they could be used as column names or if it's possible to perform such a select at all.

SQL is Microsoft's, but it would be nice to have some solution that would be supported on other database engines as well.

Any pointers or google keywords appreciated ;]

Pivot is not what you need.

Don't be afraid of multiple rows, it's often quite fast. Just use some O/RM (NHibernate or EF). You can always tweak your queries with NHProf if you have performance issues.

Without O/RM there are other two options:

• Issue an XML sub-query and retrieve all the attributes in on column as an XML list.

• Issue two queries and sort them out on the client. ADO.NET DataSets have this feature built in. Or you can do it yourself using the IDataReader.

The code for making a dynamic pivot is in my answer here