Combining columns into one column SQL Server

Question

What I'm trying to do is combine multiple columns into one. Say I have a table called Player, this table contains multiple columns e.g name, team, goalie, left back, right back, center back , left midfield.... and description.

The description column would contain name + team + left back , so description would be Ronaldo - Madrid - Forward. I can populate the description column when a player only has one position, but not for more than one position?

My SQL:

select 
    name, team, goalie,
    leftback, rightback, centerback,
    left midfield,
    ......,
    name + ' - ' + team + ' - '+ 
    CASE 
       WHEN goalie = 'yes' THEN 'Goalie' 
       WHEN leftback = 'yes' THEN 'Left Back'
       WHEN rightback = 'yes' THEN 'Right Back'
       WHEN centerback = 'yes' THEN 'Center Back'
       .......
    END AS Description
from 
    player

Output:

Ronaldo - Madrid - Forward

How can I get it to add more then one position, if the player has more than one?

Thanks

May be helpful: http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server — Dan, Nov 20 '15 at 16:19
You really should look at normalizing this. I would have a table for players, another for positions and a bridging table for PlayerPositions. That way a player can play any number of positions. — Sean Lange, Nov 20 '15 at 16:19
@Hogan I don't understand your comment. I am referring to normalization. — Sean Lange, Nov 20 '15 at 16:43
@SeanLange - No you mean 3rd normal form. This data model could be fine depending on the user's requirement. 3rd normal form is not always required or even recommended. Knee jerk 3rd normal form is a mistake in my opinion... that was my point. — Hogan, Nov 20 '15 at 17:00
I would not say that normalizing the 3rd normal is knee jerk. Sure this model can be made to work just like anything in any level of normalized form can be made to work. That doesn't mean it is a good idea. If this were in 3NF it would be easier to work with in my opinion. — Sean Lange, Nov 20 '15 at 17:15
@SeanLange We know nothing about this data and I don't know much about football -- but maybe this is a table of defensive players... then there would never be another position needed than these 3. This is a possible example why 3NF is not needed. — Hogan, Nov 20 '15 at 17:32

Juan Carlos Oropeza · Accepted Answer · 2015-11-20T16:49:14.340

5

select 
   name,
   team, 
   CASE WHEN goalie = 'yes'      THEN 'Goalie - '      ELSE '' END + 
   CASE WHEN leftback = 'yes'    THEN 'LeftBack - '    ELSE '' END + 
   CASE WHEN rightback = 'yes'   THEN 'RightBack - '   ELSE '' END + 
   CASE WHEN center back = 'yes' THEN 'Center Back - ' ELSE '' END AS Description

And you have to remove the last - on description

Version for SQL Server +2012

WITH cte as (
   SELECT
      name,
      team, 
      IIF(goalie = 'yes',      'Goalie - ', '') + 
      IIF(leftback = 'yes',    'LeftBack - ', '') + 
      IIF(rightback = 'yes',   'RightBack - ', '') + 
      IIF(center back = 'yes', 'Center Back - ', '') AS Description
   FROM Players
)
SELECT name,
       team,
       CASE LEN(Description) 
          WHEN 0 THEN Description 
          ELSE LEFT(Description, LEN(Description) - 3)
       END
FROM cte

Check In the comment for a version using STUFF

edited Nov 20 '15 at 16:49

answered Nov 20 '15 at 16:20

Juan Carlos Oropeza

47,252
12
78
118

hint... STUFF() with default of ` - ` – Hogan Nov 20 '15 at 16:24
It should be pointed out... `IIF` is not portable and is only in version greater than or equal to SQL Server 2012. – Hogan Nov 20 '15 at 16:26
@Hogan, OP say he has 2012 – Juan Carlos Oropeza Nov 20 '15 at 16:27
1

Answers on Stackoverflow are not intended for one person. If an answer only helps one person it should be closed. 100s, 1000s or more people are going to look at and use this answer. – Hogan Nov 20 '15 at 16:29
@Hogan Dont scold me I put both solutions :P. Help me with the STUFF?. I was going to do a LEFT with lenght. – Juan Carlos Oropeza Nov 20 '15 at 16:32
@JuanCarlosOropeza, the description column is coming back `NULL` – AndroidAL Nov 20 '15 at 16:34
@AndroidAL The first version need it a `ELSE ''` part. fix it Still working on the remove the last `- ` – Juan Carlos Oropeza Nov 20 '15 at 16:35
@AndroidAL Ups I lose the changes, check now. – Juan Carlos Oropeza Nov 20 '15 at 16:38
@Hogan Yes looks like I would have to use a cte and one iif for when length = 0. I tought was like a regexp replace. Will leave the `LEFT` version thanks – Juan Carlos Oropeza Nov 20 '15 at 16:52
@JuanCarlosOropeza - no problem and don't take it as a scold -- it was intended positive -- your good answer should be seen by and useful to many. – Hogan Nov 20 '15 at 17:02
@Hogan Dont worry I took it as positive, that was the :P part sorry if wasnt obvious the bazinga tone :) – Juan Carlos Oropeza Nov 20 '15 at 17:05

score 1 · Answer 2 · answered Nov 20 '15 at 20:39

1

I'm not a fan of the CASE statements and prefer to avoid them when I can. You could get away with something like:

select stuff (
              isnull(replace(replace(goalie,'no',''),'yes',' - Goalie'),'')
            + isnull(replace(replace(leftback,'no',''),'yes',' - Leftback'),'')
            + isnull(replace(replace(rightback,'no',''),'yes',' - Rightback'),'')
              ....
              ,1,3,''
             ) as 'Description'
  from Player

if the only values for goalie and the like are 'yes', 'no', and NULL.

answered Nov 20 '15 at 20:39

Brian Stork

945
2
8
14

I was testing the stuff, what if player isnt any of those category, like `defense` will stuff work with empty string and return empty string? – Juan Carlos Oropeza Nov 20 '15 at 20:53
It would return NULL. This does also assume that at least one 'yes' will be there. But, you could wrap the STUFF in an ISNULL to have it return an empty string (or something else) when the player has no 'yes' values. – Brian Stork Nov 20 '15 at 22:04
Then similar to what I did. Thanks. – Juan Carlos Oropeza Nov 20 '15 at 22:06

Combining columns into one column SQL Server

2 Answers2