sql - 将列合并为一列 SQL Server

Question

我想做的是将多列合并为一列。假设我有一个名为 Player 的表，该表包含多个列，例如 name, team, goalie, left back, right back, center back , left midfield.... and description。

description 列将包含 name + team + left back ，所以 description 将是 Ronaldo - Madrid - Forward。当一名球员只有一个位置但不能超过一个位置时，我可以填充描述栏吗？

我的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

输出:

Ronaldo - Madrid - Forward

如果玩家有多个位置，我如何让它添加多个位置？

谢谢

Answer 1

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

而且你必须删除描述中的最后一个-

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

使用STUFF检查版本的评论