none
Displaying result set fields based on the single column value? RRS feed

  • 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.


    • Edited by SQL2012BI Thursday, September 6, 2012 11:18 AM
    • Changed type Naomi NModerator Thursday, September 6, 2012 4:05 PM Question rather than discussion
    Thursday, September 6, 2012 10:02 AM

Answers

All replies

  • 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

    Thursday, September 6, 2012 10:26 AM
  • 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

    • Proposed as answer by Naomi NModerator Thursday, September 6, 2012 4:06 PM
    • Marked as answer by SQL2012BI Friday, September 7, 2012 6:17 AM
    Thursday, September 6, 2012 10:38 AM
    Answerer
  • 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.

    Thursday, September 6, 2012 10:45 AM
  • I've updated my question as it was not clear. sorry for the trouble.
    Thursday, September 6, 2012 11:19 AM