Transformation view? - 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)

Transformation view?

Author

Topic

ssamnang
Starting Member

1 Post

Posted - 2014-02-20 : 05:46:17

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
Aged Yak Warrior

545 Posts

Posted - 2014-02-20 : 06:22:28

[code]

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

[/code]

S

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources