CODEWITHSUNDEEP
sqlsql-servercase
AndroidAL
AndroidAL

Reputation: 1119

Combining columns into one column SQL Server

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

Upvotes: 2

Views: 153

Answers (2)

Brian Stork
Brian Stork

Reputation: 980

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.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 5

Related Questions