Query - 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)

Query

Author

Topic

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 13:09:23

Dear Friends;

i have a stored procedure running this query;

SELECT L.QID ,L.CLASS,L.SESSION,L.UNIT,L.CONTENT,K.LETTER,K.CONTENT FROM
tblQuestions L JOIN tblCHOICES K ON L.QID = K.QID

then i take this resultset;

QID CLASS SESSION UNIT QUESTION LETTER CHOICE
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd A ffffff
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd B lllll
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd C kkkkk
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd D mmmmm
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd E fgggg

but i want to take resultset so ;

QID CLASS SESSION UNIT QUESTION LETTER CHOICE
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd A,B,C,D,E ffffff,Kelime,Cevap,Küçük,Büyük

WHAT I HAVE TO DO TO TAKE THIS RESULTSET?

Thanks from now on..

MC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 13:19:37

[code]SELECT L.QID ,L.CLASS,L.SESSION,L.UNIT,L.CONTENT,
LEFT(l.letterlist,LEN(l.letterlist)-1) AS LETTER,
LEFT(c.contentlist,LEN(c.contentlist)-1) AS CHOICE
FROM tblQuestions L
CROSS APPLY (SELECT LETTER + ',' AS [text()]
FROM tblCHOICES
WHERE QID=l.QID
FOR XML PATH(''))l(letterlist)
CROSS APPLY (SELECT CONTENT + ',' AS [text()]
FROM tblCHOICES
WHERE QID=l.QID
FOR XML PATH(''))c(contentlist)[/code]

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 15:23:45

i took this error message ;

Msg 1011, Level 16, State 1, Line 1
The correlation name 'l' is specified multiple times in a FROM clause.

What can i do for this problem?

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 15:45:37

im sorry , i've written false
i corrected it and took the resultset

thanks a lot...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-23 : 15:45:46

change the alias for first cross apply derived table to anything else than l.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources