Sub Query Question - 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

SQL Server Development (2000)

Sub Query Question

Author

Topic

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 12:15:32

Hello, I was wanting to be able to return multiple values to a column in a subquery. Take a look, Im wanting to get all the company names and put into rxCompany, how can i do that?

select wo.woid, rxCompany = Select rsCompany
from tblwoworkorder wo
LEFT JOIN tblwoFacilityLink fl ON
fl.flFacility = wo.woFacility
LEFT JOIN tblwoAssociations ats ON
ats.asWOID = wo.WOID
LEFT JOIN tblwoResources rs ON
rs.rsID = ats.asID and rsWGID = flworkgroup
WHERE wo.woid = '0000010377')
from tblwoworkorder wo
WHERE wo.woid = '0000010377'

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 12:22:01

You should join the tables and get the resultset as follows:

woid rxCompany
0000010377 co1
0000010377 co2
0000010377 co3
.
.

rockmoose

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 12:39:18

I was more looking for something like this

woid rxCompany
0000010377 company1, company2, etc

like the results in one column, know what im sayin!?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 12:57:44

do you want the companies to be
1. comma separated or
2. each in its own column?

1. use


Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + rxCompany
FROM MyTable
WHERE Woid = 0000010377

2. search pivot/crosstab here

Go with the flow & have fun! Else fight the flow

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 13:05:15

Thanks a million buddy, you got 'er

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 13:25:32

so which one was it?

Go with the flow & have fun! Else fight the flow

benko
Starting Member

24 Posts

Posted - 2004-10-28 : 15:14:14

Just one more question. This would work if i was just bringing back one record. What if i wanted to bring back all so no where clause.

Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + rxCompany
FROM MyTable
WHERE Woid = 0000010377

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-29 : 04:00:08

that's a little harder.

easiest thing to do is to put this sql in a function which returs the CSV string.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources