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 2005 Forums
 Transact-SQL (2005)
 Transformation view?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssamnang
Starting Member

Cambodia
1 Posts

Posted - 02/20/2014 :  05:46:17  Show Profile  Reply with Quote
Hi,

I have a table to store questionnaire below:

ID PKID QuestionID Scores
1 1 01 0
2 1 02 1
3 1 03 0
4 2 01 0
5 2 02 0
6 2 03 0
.....

Is it possible to write a view to display result from above table in the following format if the given score is 0:

PKID QuestionID
1 02
2 01, 02, 03





Sam

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 02/20/2014 :  06:22:28  Show Profile  Reply with Quote


;with aCTE
AS (
		select 1 ID, 1 PKID , '01' QuestionID,0 Scores union all
		select 2, 1, '02' , 1 union all
		select 3, 1, '03', 0 union all
		select 4, 2, '01', 0 union all
		select 5, 2, '02', 0 union all
		select 6, 2, '03', 0
)

select  
A.PKID
,STUFF(B.QuestionID,1,1,'') as QuestionID
from 
	(select PKID
		from aCTE
		Group by PKID ) A

     outer apply

	 (select ' ' + QuestionID + ','
		from aCTE
		where A.PKID=aCTE.PKID
			AND aCTE.Scores=0
		order by QuestionID
	    for xml path('')
	 ) B(QuestionID)	






S



sabinWeb MCP
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.03 seconds. Powered By: Snitz Forums 2000