SQL Combining 2 rows into one

Question

I have the following query:

SELECT qn.ID, 
       qn.Title, 
       qt.Description, 
       l.DisplayName AS Language, 
       COUNT(q.ID) AS QuestionCount
  FROM dbo.Questionnaires AS qn 
  JOIN dbo.QuestionnaireText AS qt ON qn.ID = qt.QuestionnaireID 
  JOIN dbo.Questions AS q ON q.QuestionnaireID = qn.ID 
  JOIN dbo.Languages AS l ON l.ID = qt.LanguageID
 WHERE (qn.QuestionnaireTypeID = (SELECT ID 
                                    FROM QuestionnaireTypes 
                                   WHERE Value = 'Quiz'))
GROUP BY qn.ID, qn.Title, l.DisplayName, qt.Description

which outputs the following table:

36132A45-F09C-4EB5-9BD2-34A227EC03B9     Test   NULL    English 1
36132A45-F09C-4EB5-9BD2-34A227EC03B9     Test   NULL    Spanish 1
24395BC7-A890-4514-AB35-7614E226B2A5     Quiz   NULL    English 1
24395BC7-A890-4514-AB35-7614E226B2A5     Quiz   NULL    Spanish 1
03B13E61-6D7F-4597-8BB6-83889C7BFE29    G Quiz  NULL    English 6
03B13E61-6D7F-4597-8BB6-83889C7BFE29    G Quiz  NULL    Spanish 6

What I need is the query to out put the following:

36132A45-F09C-4EB5-9BD2-34A227EC03B9     Test   NULL    English, Spanish    1
24395BC7-A890-4514-AB35-7614E226B2A5     Quiz   NULL    English, Spanish    1
03B13E61-6D7F-4597-8BB6-83889C7BFE29    G Quiz  NULL    English, Spanish    6

I am at a loss at this point. Any suggestions?

Strikes me that trying to merge multiple values into a single field is an anti-pattern in SQL. Especially if it is an attempt to involve the mechanics of the data layer in the presentation layer. — MatBailie, Jul 11 '13 at 01:17
Merging the values into a single field is for an ssrs report only. There are only two languages supported by our application, English and Spanish. The 'quiz' has the same ID, but has an English and a Spanish version. The report is to display the report name, the description, the language(s) as all are not in both, and the question count of the quiz. — Jennifer Douglas, Jul 11 '13 at 01:39

score 1 · Answer 1 · answered Jul 11 '13 at 01:12

This is how you'd do it with For Xml:

SELECT qn.ID, 
       qn.Title, 
       qt.Description, 
       STUFF(
          ISNULL((SELECT ', ' + l.DisplayName
                  FROM dbo.Languages AS l
                  WHERE l.ID = qt.LanguageID
                  GROUP BY l.DisplayName
                  FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') 
           as Languages,
       COUNT(q.ID) AS QuestionCount
FROM dbo.Questionnaires AS qn 
  JOIN dbo.QuestionnaireText AS qt ON qn.ID = qt.QuestionnaireID 
  JOIN dbo.Questions AS q ON q.QuestionnaireID = qn.ID 
  JOIN dbo.QuestionnaireTypes AS qtypes ON qtypes.Id = qn.QuestionnaireTypeID
WHERE qtypes.Value = 'Quiz'
GROUP BY qn.ID, qn.Title, l.DisplayName, qt.Description

Please note, I've also moved the WHERE criteria to another JOIN.

This doesn't change my result set. Should I be changing something? — Jennifer Douglas, Jul 11 '13 at 01:46
@JenniferWeinmanDouglas -- if this isn't working, it's perhaps because of the additional aggregate with COUNT. Try moving your entire query (minus the languages) into a subquery, and then add the languages stuff back at the outer level. — sgeddes, Jul 11 '13 at 01:56

SQL Combining 2 rows into one

1 Answers1