CODEWITHSUNDEEP
sql-serversubqueryself-join
BilalAhmed
BilalAhmed

Reputation: 191

SQL Sub Queries/Self Join

This is an automatic call feedback database that stores customer feedback against every question.

I am using SQL Server 2012 I have the following data in table name [NPS_Feedback]:

CLI         CallerID    Customer_Account    Question    Feedback        Date
34622968    F22141B854  400004775250        Q1          Satisfie        2016-03-25
34622968    F22141B854  400004775250        Q2          Not Satisfied   2016-03-25
34622968    F22141B854  400004775250        Q3          Not Satisfied   2016-03-25
30227453    GED903EDL   400001913180        Q1          Not Satisfied   2016-03-25
30227453    GED903EDL   400001913180        Q2          Satisfied       2016-03-25
30227453    GED903EDL   400001913180        Q3          Not Satisfied   2016-03-25
34622968    DAED19FDE   400004775250        Q1          Satisfied       2016-03-25
34622968    DAED19FDE   400004775250        Q2          Satisfied       2016-03-25
34622968    DAED19FDE   400004775250        Q3          Satisfied       2016-03-25

Please help me with the following desire output for Reports using SQLstored procedure:

CLI     CallerID        Customer_Account    Q1             Q2             Q3              Date
34622968    F22141B854  400004775250        Satisfied      Not-Satisfied  Not-Satisfied   2016-03-25
30227453    GED903EDL   400001913180        Not-Satisfied  Satisfied      Not-Satisfied   2016-03-25
34622968    DAED19FDE   400004775250        Satisfied      Satisfied      Satisfied       2016-03-25

Please Note:

Caller ID is Unique here for every call.

Upvotes: 1

Views: 38

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Aside from using the PIVOT command, you could also use conditional aggregation:

SELECT
    CLI, 
    CallerID, 
    Customer_Account, 
    Q1 = MAX(CASE WHEN Question = 'Q1' THEN Feedback END),
    Q2 = MAX(CASE WHEN Question = 'Q2' THEN Feedback END),
    Q3 = MAX(CASE WHEN Question = 'Q3' THEN Feedback END),
    Date
FROM NPS_Feedback 
GROUP BY
    CLI, CallerID, Customer_Account, Date

ONLINE DEMO

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17136

A simple PIVOT query will work.

select CLI,CallerID,Customer_Account, [Q1],[Q2],[Q3], Date
from
(
    select 
    CLI,CallerID,Customer_Account,Question,Feedback,Date
    from [NPS_Feedback]
)s
pivot
(
    max(Feedback) for Question in ([Q1],[Q2],[Q3])
) p

Upvotes: 0

Related Questions