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.
Author |
Topic |
satish15385
Starting Member
17 Posts |
Posted - 2013-03-26 : 12:36:07
|
Hello All,I have Emplid, CourseNo ,Course Comments and Instructor Comments in a Table .each emplid can have more than one course associated with more than one course comments and Instructor Comments.i want to write a query where all [Course Comments' for a distinct emplid and Courseno appear in one row with a Paragraph split (<p> and </p>). same thing applies to Instructor Comments as well.Please guide me on how to proceed |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-03-26 : 13:38:33
|
[code]DECLARE @courses TABLE(EmplID INT, CourseNo INT, CourseComments VARCHAR(1000), InstructorComments VARCHAR(1000))INSERT @courses VALUES(1,100,'Course Comment 1','')INSERT @courses VALUES(1,100,'Course Comment 2','Instructor Comment 1')INSERT @courses VALUES(1,100,'','Instructor Comment 1')INSERT @courses VALUES(2,100,'Take your stinking paws off me you damned dirty ape','')INSERT @courses VALUES(2,200,'Yabba dabba doo','');WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY EmplID,CourseNo ORDER BY (SELECT NULL)) rownum FROM @courses)SELECT c.EmplID,c.CourseNo, (SELECT CourseComments AS p FROM cte a WHERE a.EmplID=c.EmplID AND a.CourseNo=c.CourseNo ORDER BY a.rownum FOR XML PATH(''), TYPE) CourseCommentsHTML, (SELECT InstructorComments AS p FROM cte b WHERE b.EmplID=c.EmplID AND b.CourseNo=c.CourseNo ORDER BY b.rownum FOR XML PATH(''), TYPE) InstructorCommentsHTML FROM cte cWHERE c.rownum=1 --needed to return only one row per group, DISTINCT cannot be used with XML data types[/code] |
|
|
satish15385
Starting Member
17 Posts |
Posted - 2013-03-27 : 10:41:06
|
what if i Don't want to do in HTML Output as there is a formatting error |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-03-27 : 10:47:34
|
What do you mean "formatting error"? Can you post the exact output you want from the sample data provided? |
|
|
satish15385
Starting Member
17 Posts |
Posted - 2013-03-27 : 11:13:49
|
i mean can we do it without XML Path as an Option.Lname RefNum CommentsXYZ 90 abcXYZ 90 DEFXYZ 90 GHIp 110 SampleP 110 Sample1P 110 Sample2I need OutPut asLname Refnum CommentsXYZ 90 abc,DEF,GHIP 110 Sample,Sample1,Sample2 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-03-27 : 11:22:43
|
Why can't you use XML PATH? It's the easiest option:DECLARE @t TABLE(Lname VARCHAR(10), RefNum INT, Comments VARCHAR(1000))INSERT @t VALUES('XYZ',90,'abc')INSERT @t VALUES('XYZ',90,'DEF')INSERT @t VALUES('XYZ',90,'GHI')INSERT @t VALUES('p',110,'Sample')INSERT @t VALUES('P',110,'Sample1')INSERT @t VALUES('P',110,'Sample2')SELECT DISTINCT a.Lname, a.RefNum, STUFF((SELECT ','+Comments FROM @t WHERE Lname=a.Lname AND RefNum=a.RefNum FOR XML PATH('')),1,1,'')FROM @t a |
|
|
|
|
|
|
|