Combine multiple result rows into one in SQL

Question

I have this SQL:

SELECT [Course Section], [Instructor Name], [Respondent Code], [1], [2], [3], [4], [5]
FROM [sirssoctonlineforms].[dbo].[Denormalized_V]
where term = 'ss14' and subject = 'iss' and course like '%330%'
order by subject, course, [course section], [respondent code]

And the results are:

Respondent Code     1         2              3              4              5
1281172             Average   Above Average  Above Average  Above Average  NULL
1281172             NULL      NULL           NULL           NULL           Average

What I want to see is this:

Respondent Code     1         2              3              4              5
1281172             Average   Above Average  Above Average  Above Average  Average

Is there any way I can do this?

where are the [Course Section], [Instructor Name] columns in your results? — Lamak
– Lamak, Commented May 15, 2014 at 21:08
is it possible that for some column (let's say for column [3]) there is a non-null value in more than one row? — TarasB
– TarasB, Commented May 15, 2014 at 21:13
The results are pretty much two rows for each respondent code and the nulls will always be in the same columns. It's basically the responses for a set of questions for a class, the first few pertain to the instructor and the rest are about the class as a whole. Does that help? — Michael Robinson
– Michael Robinson, Commented May 16, 2014 at 12:40

juergen d · Accepted Answer · 2014-05-15 21:08:12Z

2

SELECT [Course Section], [Instructor Name], [Respondent Code], 
       max([1]) as [1], 
       max([2]) as [2], 
       max([3]) as [3], 
       max([4]) as [4], 
       max([5]) as [5]
FROM [sirssoctonlineforms].[dbo].[Denormalized_V]
where term = 'ss14' and subject = 'iss' and course like '%330%'
group by [Course Section], [Instructor Name], [Respondent Code]
order by subject, course, [course section], [respondent code]

answered May 15, 2014 at 21:08

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

@JosephB Not my downvote, but without seeing the values for the other columns, we have no way of knowing if this query would work

Joseph B Over a year ago

@Lamak I think I agree. It might be put the original query in a subquery and then do max.

Collectives™ on Stack Overflow

Combine multiple result rows into one in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related