Not sure if possible - Append results into one

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

General SQL Server Forums

New to SQL Server Programming

Not sure if possible - Append results into one

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-24 : 14:12:36

Sorry for the confusing subject.

We have a table that tracks a company: tblCompany. A seperate table tracks facility: tblFacility. From these we can see which company has access to which Facility.

tblCompany (C) has an ID
tblFacility (F) has the Company ID and Facility ID's

ex. Companies 1, 2 / Facilities A, B, C

Company 1 has access to Facility A & C
Company 2 has access to Facility B & C

tblFacility
Company ID / Facility ID
1 , A
1 , C
2 , B
2 , C

If we run a normal Select statement we would get 4 rows of results.
How can we get the following result...

Company 1 has access to Facility A, C
Company 2 has access to Facility B, C

select 'Company ' + c.id + ' has access to Facility ' + (????) as [Description]
from tblCompany C
inner join tblFacility F on c.ID = F.ID

Does that make sense? Sorry for any confusion.

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 14:21:24

If you already know the number of facilities..you can try this..

declare @t table (cid int)
insert @t
select 1 union all
select 2

declare @r table (cid int, fid char(1))
insert @r
select 1,'A' union all
select 1,'B' union all
select 2,'B' union all
select 2,'C'

select 'Company ' + convert(varchar(1),r.cid) + ' has access to facilities ' + max(case when rownum = 1 then r.fid else null end) + ' , ' +
max(case when rownum = 2 then r.fid else null end)
from @t t
inner join (select row_number () over(partition by cid order by fid) as rownum,* from @r) r
on t.cid = r.cid
group by r.cid

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-24 : 14:41:51

We have ~100 companies so it has to be dynamic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:04:40

if you're using sql 2005 use this


select distinct 'Company ' + convert(varchar(10),t.cid) + ' has access to facilities ' + stuff((select ','+ fid from @t where cid=t.cid for xml path('')),1,1,'')
from @t t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:12:18

if sql 2000 use


CREATE FUNCTION GetFacilities
(@Cid int)
RETURNS varchar(8000) 
AS
BEGIN
DECLARE @FacilityList varchar(8000)

SELECT @FacilityList=COALESCE(@FacilityList+ ',','') + fid
FROM YourTable
WHERE Cid=@Cid

RETURN @FacilityList
END


then use it like below

SELECT distinct 'Company ' + convert(varchar(10),cid) + ' has access to facilities ' + dbo.GetFacilities(cid)
from yourtable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources