List Generation with Paras - 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 2008 Forums

Transact-SQL (2008)

List Generation with Paras

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 c
WHERE 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 Comments
XYZ 90 abc
XYZ 90 DEF
XYZ 90 GHI
p 110 Sample
P 110 Sample1
P 110 Sample2

I need OutPut as

Lname Refnum Comments
XYZ 90 abc,DEF,GHI
P 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources