none
Need help in getting required output? 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

    Thursday, September 6, 2012 12:30 PM

Answers

  • see something like this, as not tested please check for some syntex and i guess you can remove union and can have one query with questiontext in in clause as your table is same, but refer below with your origional code,

    SELECT [Poll Name],[CustomerNo.],

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

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

    INTO #temp

    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'




    select distinct [Poll Name],[CustomerNo.],a.DISPLAY, a1.LOCATION
    from #temp t
    LEFT JOIN (select [CustomerNo.],DISPLAY from #temp where CITY is not null)a on a.[CustomerNo.]=t.[CustomerNo.]
    LEFT JOIN (select [CustomerNo.],LOCATION from #temp where STAT is not null)a1 on a1.[CustomerNo.]=t.[CustomerNo.]

    • Marked as answer by SQL2012BI Friday, September 7, 2012 10:07 AM
    Friday, September 7, 2012 7:40 AM

All replies

  • It's simple, if I got it correctly. Give this a shot and let me know how it works. Thanks.

    declare @t table(poll varchar(8), account int, display varchar(8), location varchar(8))
    insert @t
    	select 'Chocs', 126508, null, 'Pack' union all
    	select 'Chocs', 126508, 'Front', null
    	
    select * from @t	
    
    select 
    	a.poll
    	,a.account 
    	,b.display 
    	,a.location 
    from 
    	@t a 
    	inner join @t b on a.poll = b.poll and a.account = b.account 
    where 
    	a.display is null
    	and b.location is null


    Thanks & Regards,
    Please do "Mark As Answer" if this helps you.

    Thursday, September 6, 2012 2:09 PM
  • Right, It's only working for the example I've given.

    But I do have more than one response for the same question and for same customer.

    In that case I'll be getting only the latest whiich I do not want.

    So, to achieve that what should I do?

    Friday, September 7, 2012 6:24 AM
  • if you do not wish to change that query which is already running, just wrap that query and group by with adding min/ max to the column where you wish to replace nulls.

    SELECT A.[Poll Name], A.[CustomerNo.], MAX(A.DISPLAY), MAX(A.LOCATION)
    FROM 
    (
    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' 
    ) A
    GROUP BY A.[Poll Name], A.[CustomerNo.]

    regards

    joon

    Friday, September 7, 2012 7:05 AM
  • see something like this, as not tested please check for some syntex and i guess you can remove union and can have one query with questiontext in in clause as your table is same, but refer below with your origional code,

    SELECT [Poll Name],[CustomerNo.],

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

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

    INTO #temp

    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'




    select distinct [Poll Name],[CustomerNo.],a.DISPLAY, a1.LOCATION
    from #temp t
    LEFT JOIN (select [CustomerNo.],DISPLAY from #temp where CITY is not null)a on a.[CustomerNo.]=t.[CustomerNo.]
    LEFT JOIN (select [CustomerNo.],LOCATION from #temp where STAT is not null)a1 on a1.[CustomerNo.]=t.[CustomerNo.]

    • Marked as answer by SQL2012BI Friday, September 7, 2012 10:07 AM
    Friday, September 7, 2012 7:40 AM
  • Thanks skc_chat.

    I've got the required result. I've removed join, and then did what you suggested and it worked.

    Thanks.

    Friday, September 7, 2012 10:08 AM