CODEWITHSUNDEEP
sqlsql-serverpivot
PBG
PBG

Reputation: 9654

Select matching rows as columns

I think it would be quickest to show by example, so here goes a simplified version of my problem. I am using SQL Server.

This is the data model

SURVEY       QUESTION        ANSWER
------       --------        -------
surveyId     questionId      answerId
             questionText    surveyId
                             questionId
                             answerText

Here's some sample data

SURVEY          QUESTION
 ----------      ------------ --------------
| surveyId |    | questionId | questionText |
 ----------      ------------ --------------
| 1        |    | 1          | Name         |
| 2        |    | 2          | E-Mail       |
 ----------     | 3          | Address      |
                | 4          | Phone        |
                 ------------ --------------
ANSWER
 -----------------------------------------------
| answerId | surveyId | questionId | answerText |
 -----------------------------------------------
| 1        | 1        | 1          | John       |
| 2        | 1        | 2          | [email protected] |
| 3        | 1        | 3          | New York   |
| 4        | 1        | 4          | 1112223344 |
| 5        | 2        | 1          | Pete       |
| 6        | 2        | 2          | [email protected] |
| 7        | 2        | 3          | Boston     |
| 8        | 2        | 4          | 5556667788 |
 -----------------------------------------------

Finally, here's what I'd like to select

RESULT
 ------------------------------
| surveyId | name | email      |
 ------------------------------
| 1        | John | [email protected] |
| 2        | Pete | [email protected] |
 ------------------------------

I'd like to be able to specify at run time which columns I need in my final table. Right now I can get this result by doing the following

SELECT rName.surveyId, rName.requestor, rEmail.email 
FROM (SELECT a.answerText AS name, a.surveyId
      FROM answer a INNER JOIN question q ON a.questionId = q.questionId 
      WHERE a.surveyId = @surveyId AND q.questionText = 'Name') AS rName
INNER JOIN 
     (SELECT a.answerText AS name, a.surveyId
      FROM answer a INNER JOIN question q ON a.questionId = q.questionId 
      WHERE a.surveyId = @surveyId AND q.questionText = 'E-Mail') AS rEmail
ON rName.questionnaireId = rEmail.questionnaireId

As you can see, it's ugly, and contains duplicate code. Is there a way to do this cleaner?

Upvotes: 0

Views: 148

Answers (4)

Christian
Christian

Reputation: 19

Might not be the "correct" usage of PIVOT, but the following will do the trick (req. SQL2005 or greater):

SELECT surveryId, [1] AS [name], [2] AS [e-mail]
FROM 
( SELECT a.surveryId, q.QuestionId, a.answerText
FROM dbo.Question q
INNER JOIN dbo.Answer a ON a.questionId = q.QuestionId
) tmp
PIVOT (
    MAX(answerText)
    FOR questionId IN ([1], [2])
) PvtTable

Upvotes: 1

roman
roman

Reputation: 117530

SQL FIDDLE EXAMPLE

select *
from
(
  select
      s.surveyid,
      q.questionText,
      a.answerText
  from SURVEY as s
      cross join QUESTION as q
      left outer join ANSWER as a on a.questionId = q.questionId and a.surveyId = s.SurveyId
) as M
pivot
(
  min(M.answerText)
  for M.questionText in ([Name], [E-Mail], [Address], [Phone])
) as P

Upvotes: 2

Taryn
Taryn

Reputation: 247850

SQL Server has a PIVOT function that is available for this type of query. If you know the values that you want to transform you can hard-code the values:

select surveyid, name, [e-mail]
from
(
  select a.answertext,
    q.questiontext,
    s.surveyid
  from survey s
  left join answer a
    on s.surveyid = a.surveyid
  left join question q
    on a.questionid = q.questionid
) src
pivot
(
  max(answertext)
  for questiontext in ([name], [e-mail])
) p

See SQL Fiddle with Demo

Now if you have an unknown number of values, you can use dynamic sql to pivot the data:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(questiontext) 
                    from question
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT surveyid, ' + @cols + ' from 
             (
                select a.answertext,
                  q.questiontext,
                  s.surveyid
                from survey s
                left join answer a
                  on s.surveyid = a.surveyid
                left join question q
                  on a.questionid = q.questionid
            ) x
            pivot 
            (
                max(answertext)
                for questiontext in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Upvotes: 2

John Woo
John Woo

Reputation: 263843

SELECT  a.surveyID,
        MAX(CASE WHEN c.questionText = 'Name' THEN b.answerText ELSE NULL END) name,
        MAX(CASE WHEN c.questionText = 'E-Mail' THEN b.answerText ELSE NULL END) email
FROM    Survey a
        INNER JOIN Answer b
            ON a.surveyID = b.surveyID
        INNER JOIN Question c
            ON b.questionID = c.questionID
GROUP BY a.surveyID

Upvotes: 2

Related Questions