skathan skathan - 6 months ago 34
SQL Question

Pivoting and adding new column in SQL Server Management Studio 2016?

I have a database that looks like this:

IndexID QuestionID AnswerGiven
1 3 Phone
1 7 Strongly Agree
2 8 Agree
2 5 Yes
2 3 Chat
3 6 NULL
3 3 Phone
4 3 Web
4 7 Disagree

And I want to write a script to essentially pull out Question #3 into its own column called ContactChannel, like this:

IndexID QuestionID ContactChannel AnswerGiven
1 7 Phone Strongly Agree
2 8 Chat Agree
2 5 Chat Yes
3 6 Phone Disagree
4 7 Web Disagree

I'm new to SQL, but I suspect this has something to do with pivoting and sub-queries, and I know it can vary by database Any ideas for SSMS 2016?

Answer Source

You can use a query like the following:

SELECT t1.IndexID, t1.QuestionID, t2.AnswerGiven AS ContactChannel, t1.AnswerGiven
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.IndexID = t2.IndexID AND t2.QuestionID = 3
WHERE t1.QuestionID <> 3;

The query is in ANSI SQL and should work in any RBDMS.

Demo here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download