SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 List Generation with Paras
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satish15385
Starting Member

17 Posts

Posted - 03/26/2013 :  12:36:07  Show Profile  Reply with Quote
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

USA
15681 Posts

Posted - 03/26/2013 :  13:38:33  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

satish15385
Starting Member

17 Posts

Posted - 03/27/2013 :  10:41:06  Show Profile  Reply with Quote
what if i Don't want to do in HTML Output as there is a formatting error
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 03/27/2013 :  10:47:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
What do you mean "formatting error"? Can you post the exact output you want from the sample data provided?
Go to Top of Page

satish15385
Starting Member

17 Posts

Posted - 03/27/2013 :  11:13:49  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 03/27/2013 :  11:22:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000