Displaying result set fields based on the single column value?

Question

Hi Experts,

Below is the Query and am getting the below result set as

Poll Name       Account No.                DISPLAY             LOCATION

Chocs               126508                      NULL                  Pack

Chocs                126508                     Front                  NULL

My requirement is to display as below...

Poll Name       Account No.                DISPLAY             LOCATION

Chocs               126508                      Front                   Pack

SELECT [Poll Name],[CustomerNo.],

DISPLAY  = CASE WHEN QuestionText = 'Where is it?' THEn ResponseText END,

LOCATION = CASE WHEN QuestionText = 'What to show?' THEN ResponseText END

FROM (

SELECT  P.Name AS [Poll Name]

       ,A.No AS [CustomerNo.]

       ,PQ.QuestionText

       ,PRP.ResponseText

FROM Poll_Result_Question PRQ (NOLOCK)

JOIN Poll_Response PRP (NOLOCK) ON PRP.ID = PRQ.Response_ID

JOIN Poll_Poll_Question PPQ (NOLOCK) ON PPQ.Question_ID = PRQ.Question_ID

JOIN Poll_Question PQ (NOLOCK) ON PQ.ID = PPQ.Question_ID

JOIN Poll_Result PR (NOLOCK) ON pSR.ID = PRQ.Result_ID

JOIN Account A (NOLOCK) ON A.ID = SR.Entity2_ID

JOIN Poll P (NOLOCK) ON P.ID = PR.Survey_ID

WHERE P.PollName= 'Chocs'

AND PQ.QuestionText IN('What to show?')

UNION

SELECT  P.Name AS [Poll Name]

       ,A.No AS [CustomerNo.]

       ,PQ.QuestionText

       ,PRP.ResponseText

FROM Poll_Result_Question PRQ (NOLOCK)

JOIN Poll_Response PRP (NOLOCK) ON PRP.ID = PRQ.Response_ID

JOIN Poll_Poll_Question PPQ (NOLOCK) ON PPQ.Question_ID = PRQ.Question_ID

JOIN Poll_Question PQ (NOLOCK) ON PQ.ID = PPQ.Question_ID

JOIN Poll_Result PR (NOLOCK) ON pSR.ID = PRQ.Result_ID

JOIN Account A (NOLOCK) ON A.ID = SR.Entity2_ID

JOIN Poll P (NOLOCK) ON P.ID = PR.Survey_ID

WHERE P.PollName= 'Chocs'

AND SQ.QuestionText IN('Where is it?')

)AA

WHERE [AccountNo.] = '126508' 

Please help

Thanks in advance

Sorry Ifmy earlier version of asking was not clear.

Answer 1

Do a Self Join, like this:

select a.PollName, a.CustomerNo, a.ResponseText as Display, b.ResponseText as location

from your_table a

join your_table b

on a.PollName = a.PollName

and a.CustomerNo = b.CustomerNo

and a.QuestionText = 'Where is it?'

and b.QuestionText = 'What to show?'

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

Sorry, untested

SELECT PollName, CustomerNo,

MAX(CASE WHEN QuestionText='Where is it?'  THEN  ResponseText END display,

MAX(CASE WHEN QuestionText=' What to show?'  THEN  ResponseText END location

FROM  tbl GROUP BY PollName, CustomerNo

    

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 3

try this :

declare @table table(PollName  varchar(10),CustomerNo INT,QuestionText   varchar(100),ResponseText varchar(20))
insert into @table
values('AAA',1234,'Where is it?','Front'),
('AAA',1289,'Where is it?','Back'),
('AAA',1234,'What to show?','Pack'),
('AAA',1289,'What to show?','Bits')

select 
	t1.PollName,
	t1.CustomerNo,
	t1.ResponseText as Display,
	t2.ResponseText as location
from @table t1
INNER join @table t2 on t1.CustomerNo = t2.CustomerNo
AND t1.QuestionText = 'Where is it?'
AND t2.QuestionText = 'What to show?'

---

Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

I've updated my question as it was not clear. sorry for the trouble.