Querying Info For A Report - 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

General SQL Server Forums

New to SQL Server Programming

Querying Info For A Report

Author

Topic

MattieMich7
Starting Member

2 Posts

Posted - 2009-04-09 : 21:14:12

Table1
--------------
LocID LangID
100 1
100 2
200 1

Table2
--------------
LangID Desc
1 English
2 Spanish

As part of a report I'm creating, I need to list out the languages by location. So based on the tables above, I want to write a query that will result in the following:

LocID Languages
100 English, Spanish
200 English

Any help would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:12:19

[code]
;With CTE(LocID,Language)
AS
(
SELECT t1.LocID,t2.Desc AS Language
FROM Table1 t1
JOIN Table2 t2
ON t2.LangID=t1.LangID
)

SELECT DISTINCT LocID,
STUFF((SELECT ',' + Language FROM CTE WHERE LocID=c.LocID FOR XML PATH('')),1,1,'')
FROM CTE c
[/code]

pmohans123
Starting Member

5 Posts

Posted - 2009-04-10 : 06:42:56

Hi,

Please try with this example, please replace temp table (#temp1 and #temp2) to your real table.

Create table #temp1 (locid int, [langid] int)

Create table #temp2 ([langid] int, [Desc] varchar(50))

Insert into #temp1 values (100, 1)
Insert into #temp1 values (100, 2)
Insert into #temp1 values (200, 1)
Insert into #temp2 values (1, 'English')
Insert into #temp2 values (2, 'Spanish')

Select * from #temp1
Select * from #temp2

Create Table #temp3(id int identity(1,1), locid int, Descripton varchar(500))

Insert into #temp3 (locid)
Select distinct locid from #temp1

Declare @var varchar(100),
@Cnt int,
@Totcnt int,
@Locid int

Set @Cnt = 1
Select @Totcnt = count(*) from #temp3

While @cnt <= @Totcnt
Begin

Set @var = Null

Select @locid = locid from #temp3 where id = @cnt

Select @var = Isnull(@var + ',','') + [desc] from #temp1 a inner join #temp2 b on
a.[langid]=b.[langid] where locid = @locid

Update #temp3 set Descripton = @var where locid = @locid

Set @cnt = @cnt + 1
End

Select locid, Descripton from #temp3

Drop table #temp1
Drop table #temp2
Drop table #temp3

I don’t know whether it is a feasible solution or not, but you will get the correct answer.

Thanks,
Mohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:53:48

why use loop? always try to go for set based solution. thats faster.

MattieMich7
Starting Member

2 Posts

Posted - 2009-04-10 : 09:25:51

Thank you visakh16, that worked perfectly.

I'm a SQL newbie, so I am not at all familiar with the STUFF function you used. Is there a good place to find more info on that or could you elaborate on what your approach was?

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources