How to combine more than one statement into one?

Question

Alright, here is a little scoop of what I am trying to accomplish. I have a case statement in the select statement. Here is what I have...:

CASE pu.Group_Value 
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B' 
    WHEN 3 then 'C' 
    WHEN 4 then 'D' 
    WHEN 5 then 'E' 
    WHEN 6 then 'F' 
    WHEN 7 then 'G' 
END AS Groups,

The issue I am having is this case statement can have more than one return that comes back. What I need to do is be able to combine these into one. I end up having 7 rows coming back, but only need one row with all 7 letters in that rows column...

Thanks!

Looks like you need to concatenate rows. Are you using SQL Server or some other DBMS? — PinnyM, Dec 27 '12 at 19:15
No it can't. The case statement will be evaluated per row. It doesn't generate new rows. Could you further explain, perhaps with examples of what your starting data looks like, the scenarios that lead to 7 rows coming back and the desired end state? — billinkc, Dec 27 '12 at 19:16
Ok I can have all seven values comming back and what I do is I take these values (1,2,3,4,5,6 & 7) and assign them a letter. But not at all times do these come back (might only get 1 or 2). What I need to do is be able to combine the "WHEN" together so it would be like (A, B, C) and so on... Right now I will return only one row and that is what I need, but the column that needs these values only shows the one letter and populates more rows with the different letters... — Trevor, Dec 27 '12 at 19:23
Excellent, you are looking to `concatenate` these values together based on some grouping criteria (all rows with same ID go together). Does order matter? Is 1,2,3,4,5 the same to the consumer of the data as 5,4,3,2,1 and 1,3,5,4,2 ? Also, when you respond to comments, it's usually helpful to edit those into your question and then alert the questioner by using the @ handle — billinkc, Dec 27 '12 at 19:28
Yes @billinkc (sorry)! Order does kind of matter in this situation... — Trevor, Dec 27 '12 at 19:32
You can do it using `FOR XML PATH` like this - http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — PinnyM, Dec 27 '12 at 19:43

PinnyM · Accepted Answer · 2012-12-27T20:05:02.997

1

Use FOR XML PATH. For example:

SELECT STUFF(
  (
    SELECT ','+ CASE pu.Group_Value
                  WHEN 1 THEN 'A'
                  ...
                END 
    FROM pu
    FOR XML PATH('')
  ),1,1,'') as Groups

Just make sure you don't leave any room in your CASE logic for NULLs or it will make the whole string NULL.

edited Dec 27 '12 at 20:05

answered Dec 27 '12 at 19:46

PinnyM

35,165
3
73
81

Could I get a little better example, was just a little confusing for me ...? @PinnyM – Trevor Dec 27 '12 at 19:52
1

See this [sqlfiddle](http://sqlfiddle.com/#!3/c1211/12) to understand how this is built. If you have a more complex query, please post and I'll show you how to use them with STUFF and FOR XML PATH – PinnyM Dec 27 '12 at 20:12

score 0 · Answer 2 · answered Dec 27 '12 at 19:17

What you probably want to do is a GROUP BY pu.Group_Value.

The number of rows returned by this depend on the rest of the SQL query besides what you've included above. If you only want to return one row, SELECT TOP 1 ... is your quickest bet, otherwise you will need to be much more specific with your WHERE clause criteria or evaluate using a GROUP BY, which might change what columns you include in your SELECT.

How to combine more than one statement into one?

2 Answers2