0

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

Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
Marius
  • 205
  • 1
  • 7
  • Please write an example input and the desired output. – Adam Matan Dec 09 '10 at 12:16
  • 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 Dec 09 '10 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]: http://stackoverflow.com/questions/4386346/sql-column-from-all-rows-returned-from-a-join-into-a-single-column [2]: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string [3]: http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery – TomaszK Dec 09 '10 at 12:26

2 Answers2

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.

serega
  • 564
  • 1
  • 5
  • 14
0

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

Community
  • 1
  • 1
smirkingman
  • 6,167
  • 4
  • 34
  • 47