CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Trevor
Trevor

Reputation: 8004

How to combine more than one statement into one?

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!

Upvotes: 0

Views: 155

Answers (2)

PinnyM
PinnyM

Reputation: 35533

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.

Upvotes: 1

gazarsgo
gazarsgo

Reputation: 94

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.

Upvotes: 0

Related Questions