sql select multiple rows into one column

Question

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 querying 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 ;]

What you are describing will require dynamic SQL as you don't know up-front how many columns the output will have (it depends on the data in the tables). So, have a Google for "Dynamic SQL". And, yes, a pivot would also do the job. — John Pickup
– John Pickup, Commented Dec 9, 2010 at 12:18
Jurying by the title, what you're looking for is a duplicate of [question 4386346][1], which turned out to be a duplicate of [question 194852][2] and [question 6899][3]. But maybe that's only a part of your problem. [1]: stackoverflow.com/questions/4386346/… [2]: stackoverflow.com/questions/194852/… [3]: stackoverflow.com/questions/6899/… — TomaszK
– TomaszK, Commented Dec 9, 2010 at 12:26

serega · Accepted Answer · 2010-12-09 12:23:22Z

0

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.

answered Dec 9, 2010 at 12:23

serega

5741 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:11:50Z

0

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

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Dec 9, 2010 at 13:03

smirkingman

6,4365 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

sql select multiple rows into one column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related