将SQL表行与单个文本中的条件连接起来

I have a SQL table like this

我有一个这样的SQL表

col1     col2      col3
1         0         1
1         1         1
0         1         1
1         0         0
0         0         0

I am expecting output as like this

我希望输出是这样的

col1     col2      col3     NewCol
1         0         1        SL,PL
1         1         1        SL,EL,PL
0         1         1        EL,PL
1         0         0        SL
0         0         0        NULL

The condition for this is if col1>0 then SL else ' ', if col2>0 EL else ' ', if col3>0 PL else ' '

这里的条件是如果col1>那么SL else ' '如果col2>0 EL else '如果col3>0 PL else '

I tried to use Concatenate many rows into a single text string? but didn't able to achieve the desired result properly

我尝试将许多行连接到一个文本字符串中?但未能达到预期的效果

I have tried It is working fine with a message

我已经试过了，这条消息很有效

Invalid length parameter passed to the LEFT or SUBSTRING function.

传递给左或子字符串函数的无效长度参数。

WITH CTE AS (
   SELECT col1, col2, col3,
          CASE WHEN col1 > 0 THEN 'SL,' ELSE '' END +
          CASE WHEN col2 > 0 THEN 'EL,' ELSE '' END +
         CASE WHEN col3 > 0 THEN 'PL,' ELSE '' END AS NewCol
   FROM   Employee
)
SELECT col1, col2, col3,
       substring(NewCol, 1, len(NewCol) - 1) AS NewCol
FROM   CTE

But again my last condition is not matching if all columns is 0 then I have to show NULL as per desired output.

但最后一个条件是不匹配如果所有列都是0，那么我必须按照期望的输出显示NULL。

Find the attach fiddle http://sqlfiddle.com/#!6/2bd6a/1

找到attach小提琴http://sqlfiddle.com/#!6/2bd6a/1

3 个解决方案

#1

The issue with your code example is that when all columns are 0 then the length is 0 and the substring function will throw an error.

代码示例的问题是，当所有列都为0时，长度为0，子字符串函数将抛出一个错误。

Use nullif to fix it: substring(NewCol, 1, len(nullif(NewCol,'')) - 1) AS NewCol

使用nullif来修复它:substring(NewCol, 1, len(nullif， ") - 1)作为NewCol

#2

You could also change to appending the delimiter on the front and use STUFF.

您还可以更改为在前面添加分隔符并使用东西。

STUFF('',1,1,'') will return NULL rather than an error.

STUFF(“、1、1”)将返回NULL而不是错误。

WITH 
Employee(col1, col2, col3) AS (
SELECT 1,1,1 UNION ALL
SELECT 0,0,0
),
CTE AS (
   SELECT col1, col2, col3,
          CASE WHEN col1 > 0 THEN ',SL' ELSE '' END +
          CASE WHEN col2 > 0 THEN ',EL' ELSE '' END +
          CASE WHEN col3 > 0 THEN ',PL' ELSE '' END AS NewCol
   FROM   Employee
)
SELECT col1,
       col2,
       col3,
       STUFF(NewCol, 1, 1, '')
FROM   CTE

Returns

+------+------+------+------------------+
| col1 | col2 | col3 | (No column name) |
+------+------+------+------------------+
|    1 |    1 |    1 | SL,EL,PL         |
|    0 |    0 |    0 | NULL             |
+------+------+------+------------------+

#3

You have to check with NULLIF to do this trick

你必须与NULLIF进行检查才能完成这个技巧

Two ways

两种方式

SELECT col1, col2, col3,
    nullif(CASE WHEN col1  = 1 THEN 'SL,' ELSE '' END +
           CASE WHEN col2  = 1 THEN 'EL,' ELSE '' END +
           CASE WHEN col3  = 1 THEN 'PL,' ELSE '' END,'') AS NewCol 
FROM Employee

或

SELECT 
    col1,
    col2,
    col3,
    substring(nullif(NewCol,''), 1, len(NewCol) - 1) AS NewCol
FROM  
    CTE

#1