Concatenate SQL table rows with conditions in single text

Question

I have a SQL table like this

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 ' '

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.

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

There is still no question being asked here. Please add more details, and add a question. The question you linked to has multiple answers, please share which one of those answers you used in your own sql statement and include the errors, if any. — AStopher, Sep 19 '15 at 10:22
BTW, is NewCol your new column in table ?In that case it is very wrong to store such value.If it is your output that you want to display then no need of such computed column,you can do so such manipulation with the help of your class property ,thus avoiding such complicated query. — KumarHarsh, Sep 19 '15 at 18:19

score 3 · Accepted Answer · answered Sep 19 '15 at 10:52

3

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.

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

answered Sep 19 '15 at 10:52

jpw

44,361
6
66
86

Martin Smith · Answer 2 · 2015-09-19T11:02:10.977

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.

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             |
+------+------+------+------------------+

edited Sep 19 '15 at 11:02

answered Sep 19 '15 at 10:58

Martin Smith

438,706
87
741
845

This also worked. But I have never used STUFF. Thanks for sharing :) – Sabyasachi Mishra Sep 19 '15 at 11:01
@SabyasachiMishra - Why "But"? Is it a negative to use something new? – Martin Smith Sep 19 '15 at 11:06
Ahh maybe you misunderstood my words. I told I have never used as I have never heard of it. – Sabyasachi Mishra Sep 19 '15 at 11:09
1

@SabyasachiMishra - So you probably didn't want the word "but" there. [used to introduce a phrase or clause contrasting with what has already been mentioned.](https://www.google.co.uk/webhp?sourceid=chrome-instant&rlz=1C1DSGP_enGB502GB502&ion=1&espv=2&ie=UTF-8#q=define:but) - It reads as though "It Works" (positive) then "but" would usually introduce a negative. – Martin Smith Sep 19 '15 at 11:15
1

@MartinSmith: super liked your answer!! – Nitin Tripathi Sep 19 '15 at 11:19

score 1 · Answer 3 · answered Sep 19 '15 at 11:07

You have to check with NULLIF to do this trick

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

OR

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

Concatenate SQL table rows with conditions in single text

3 Answers3