Help Needed - 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 2005 Forums

Transact-SQL (2005)

Help Needed

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-20 : 10:02:54

Hi,
My Table structure is:

Table 3:
ID SID BID
1 1 1
1 1 2
1 1 3

Table2:
SID SNAME
1 Ram

Table1:
BID BNAME
1 King
2 Tiger
3 Lion

i want to construct a query which will produce op like this

ID SNAME BNAME
1 Ram King,Tiger,Lion

Please Show me a way

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 10:14:45

[code]DECLARE @Table3 TABLE
(
ID INT,
SID INT,
BID INT
)

INSERT @Table3
SELECT 1, 1, 1 UNION ALL
SELECT 1, 1, 2 UNION ALL
SELECT 1, 1, 3

DECLARE @Table2 TABLE
(
SID INT,
SNAME SYSNAME
)

INSERT @Table2
SELECT 1, 'Ram'

DECLARE @Table1 TABLE
(
BID INT,
BNAME SYSNAME
)

INSERT @Table1
SELECT 1, 'King' UNION ALL
SELECT 2, 'Tiger' UNION ALL
SELECT 3, 'Lion'

;WITH Yak (ID, SNAME, BNAME)
AS (
SELECT t3.ID,
t2.SNAME,
t1.BNAME
FROM @Table3 AS t3
INNER JOIN @Table2 AS t2 ON t2.SID = t3.SID
INNER JOIN @Table1 AS t1 ON t1.BID = t3.BID
)

SELECT d.ID,
d.SNAME,
STUFF(w.f, 1, 1, '') AS BNAME
FROM (
SELECT ID,
SNAME
FROM Yak
GROUP BY ID,
SNAME
) AS d
CROSS APPLY (
SELECT DISTINCT ',' + y.BNAME
FROM Yak AS y
WHERE y.ID = d.ID
AND y.SNAME = d.SNAME
ORDER BY ',' + y.BNAME
FOR XML PATH('')
) AS w(f)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:17:08

[code];With Yak (BNAME,SNAME) AS
(
SELECT t1.BNAME
,t2.SNAME
FROM Table1 t1
INNER JOIN Table3 t3
On t3.BID=t1.BID
INNER JOIN Table2 t2
ON t2.SID=t3.SID
)

SELECT t1.SNAME,LEFT(nl.namelist,LEN(nl.namelist)-1)
FROM (SELECT DISTINCT SNAME FROM Yak) t1
CROSS APPLY(SELECT BNAME +','
FROM Yak
WHERE SNAME=t1.SNAME
FOR XML PATH('')) nl(namelist)[/code]

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-20 : 10:19:28

Can u please tell me what is that Yak? is it a predefined function?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:21:59

quote:
Originally posted by rammohan

Can u please tell me what is that Yak? is it a predefined function?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Nope its called CTE (Common Table Expressions). its like derived table. look into syntax of CTE in books online

http://msdn.microsoft.com/en-us/library/ms190766.aspx

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-20 : 10:29:19

Thank u

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources