Home SQL Concatenation using case statement
Reply: 2

SQL Concatenation using case statement

genie
1#
genie Published in 2017-11-13 21:28:54Z

I have a table which looks like this:

**ID    col1  col2  col3  col4  col5**    
1       abc   NULL  NULL  NULL  NULL    
2       ght   cde   NULL  NULL  NULL    
3       atr   dgf   aft   NULL  NULL     
4       asd   rty   tyu   xyz   NULL     
5       abc   pqr   xyz   rst   rty    
6       qwe   rty   ghj   rty   tyu  

I want it change in this form:

**ID    NewCol**    
1   abc    
2   ght/cde    
3   atr/dgf/aft    
4   asd/rty/tyu/xyz    
5   abc/pqr/xyz/rst/rty    
6   qwe/rty/ghj/rty/tyu   

The query I wrote is:

     SELECT ID,     
    NewCol = CASE WHEN col1 IS NOT NULL AND col2 IS NULL THEN col1    
                WHEN col1 IS NOT NULL  AND col3 IS NULL THEN col1+'/' + col2    
                WHEN col1 IS NOT NULL AND col4 IS  NULL THEN col1+'/'+ col2 + '/' + col3    
                WHEN col1 IS NOT NULL AND col5 IS NULL THEN col1+'/' + col2 + '/' + col3+ '/' + col4     
                WHEN col1 IS NOT NULL AND col5 IS not NULL THEN col1+'/' + col2 + '/' + col3+ '/' + col4 + '/' + col5     
                ELSE NULL     
  END     
   FROM dbo.Temp 

If we have like 10 columns then I thought may be this is not an efficient way to write this.
Can you help me find a better solution to this one?
Thanks.

Gordon Linoff
2#
Gordon Linoff Reply to 2017-11-13 21:31:43Z

I think this is simpler:

select (col1 + coalesce('/' + col2, '') + coalesce('/' + col3, '') + 
        coalesce('/' + col4, '') + coalesce('/' + col5, '')
       )

This assumes that col1 always has a value.

Martin Smith
3#
Martin Smith Reply to 2017-11-13 21:46:35Z

You could use

col1 + concat('/' + col2, '/' + col3, '/' + col4, '/' + col5)

If on 2012 or later version

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334304 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO