Varchar SUM / Cross Tab type query in SQL (ms)

dor
01-12-2004, 09:53 AM
I have 2 tables:
Table 1
IDf |Desc
1 |One
2 |Two
3 |Three
4 |Four

Table 2
IDa |IDf
1 |1
1 |2
2 |2
2 |2

What I need is some way of presenting the data like this:
SUM(IDa) as NoCols, <A Function>(Desc) as AllDesc From <etc> Where IDa = 1

Results:

NoCols |AllDesc
2 |One,Two

Can this be done? Thanks in advance!

loquin
01-12-2004, 10:44 AM
I'm trying to figure out your table definitions from your description.

Are you trying to say that table2 holds the field names from table1 in the second column? If so, how can you possibly relate the tables? A relational database relates the VALUES in one table to the VALUES in another table.

dor
01-12-2004, 11:09 AM
Sorry, I will try to define it using a better example:

Table 1 (Fruit)

FruitID | FruitName
1 | Orange
2 | Apple
3 | Tomato
4 | Lemon

Table 2 (Fruit Orders)

OrderID | FruitID
1 | 1
1 | 2
2 | 2
2 | 2
3 | 1
3 | 3
etc..

And I want a result set (say for OrderID 1:)

OrderID | List
1 | Orange, Apple

(I didn't design the DB I hasten to add)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum