Get Column value as row - 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)

Get Column value as row

Author

Topic

suresh_dotnet
Starting Member

6 Posts

Posted - 2009-05-06 : 05:08:11

Hi :

below is my Requirement:

User Table

UserID UserName

1 Kumar

FeedbackQA -Table

FeedbackQAId Qusetions Answers

1 Are u a Tax Payer? Yes

2 Have you Paid Tax? Yes

FeedbackReport Table

UserID FeedbackQAId Answers

1 1 Yes
1 2 Yes

The above are the DB tables

UserName Are u a Tax Payer ? Have you Paid Tax?

Kumar Yes Yes

Can any Send me the Query to achieve this! its very urgent

Thanks:
Suresh

suresh

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-06 : 05:12:30

use pivot, it will be more useful for u

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-06 : 05:27:06

Try this Once,

select u.username,
max(t.[Are u a Tax Payer?]) as [Are u a Tax Payer?],
max(t.[Have you Paid Tax?]) as [Have you Paid Tax?]
from @usertable u
inner join @FeedbackReport fr on fr.userid = u.userid
inner join ( select FeedbackQAId,[Are u a Tax Payer?],[Have you Paid Tax?]
from( select * from @FeedbackQA ) p
pivot ( max(answers) for Qusetions in ([Are u a Tax Payer?],[Have you Paid Tax?]) ) as p2 ) t
on t.FeedbackQAId = fr.FeedbackQAId
group by u.username

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-06 : 07:24:07

[code]
SELECT u.username,
MAX(CASE WHEN f.Qusetions ='Are u a Tax Payer?' THEN fr.answers END) AS 'Are u a Tax Payer?',
MAX(CASE WHEN f.Qusetions ='Have you Paid Tax?' THEN fr.answers END) AS 'Have you Paid Tax?'
FROM @user u
INNER JOIN @FeedbackReport fr ON fr.userid = u.userid
INNER JOIN @FeedbackQA f ON f.FeedbackQAId = fr.FeedbackQAId
GROUP BY username
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources