Vfrdtyky

i have Concatenating the multiple Columns and i want the count of how many columns are Concatenated

the query output and expected output are

for Concatenation

select  ConcateColumn = STUFF(

                              COALESCE('* ' + RTRIM(col1),'') 

                             +COALESCE('* ' + RTRIM(col2),'') 

                             +COALESCE('* ' + RTRIM(col4),'') 

                             +COALESCE('* ' + RTRIM(col3),'') 

                       , 1, 2, '') 

table

===================================

| col1   | col2  |  col3  |  col4 | 

===================================

|  1     | 2     |  NULL  | NULL  |

|  NULL  | NULL  |  NULL  | NULL  |

|  1     | NULL  |  NULL  | NULL  |

|  NULL  | 2     |  3     | 4     |

|        | NULL  |  NULL  | NULL  |       

==================================

the resulted output

 ==============

|ConcateColumn|

 ==============

|1*2          |

|NULL         |

|1            |

|2*3*4        |

|             |

===============

Expected Output

------------------------

| Count | ConcateColumn |

-------------------------

|   2   |     1*2       |

|   0   |     NULL      |

|   1   |     1         |

|   3   |     2*3*4     |

|   0   |               |

-------------------------

if i get the count that is more than enough

You can count the number of * in the concatenated string:

with cte as 

 (

   select  

      ConcateColumn = STUFF(  COALESCE('* ' + NULLIF(RTRIM(col1),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col2),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col3),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col4),''),'') 

                       , 1, 2, '') 

   FROM T

 )

select ConcateColumn,

   -- how many '*' have been removed?

   coalesce(len(ConcateColumn) - len(replace(ConcateColumn, '*', '')) + 1, 0)

from cte 

;

Of course, this will return wrong numbers if your data contains *

You can try this.

select  (SELECT count(NULLIF(val,''))

        FROM   (VALUES(col1),(col2),(col3), (col4)) v (val)) 'COUNT',

          ConcateColumn = STUFF(

                              COALESCE('* ' + NULLIF(RTRIM(col1),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col2),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col3),''),'') 

                             +COALESCE('* ' + NULLIF(RTRIM(col4),''),'') 

                       , 1, 2, '')

FROM T

If you want to count without NULL and '' you can try to use NULLIF function.

sqlfiddle

In SQL Server 2017+, you can simply do:

select v.*

from t cross apply

     (select count(*) as cnt,

             string_agg(rtrim(col), '* ') within group (order by ord) as ConcateColumn

      from (values (1, col1), (2, col2), (3, col4), (4, col3)

           ) v(col, ord)

      where col is not null

     ) v;

In earlier versions, I would probably follow the same structure that you have:

select ( (case when col1 is not null then 1 else 0 end) +

         (case when col2 is not null then 1 else 0 end) +

         (case when col4 is not null then 1 else 0 end) +

         (case when col3 is not null then 1 else 0 end)

       ) as cnt

 select sum(

case when col1 = '' then 0 when col1 is null then 0 else 1 end +

case when col2 = '' then 0 when col2 is null then 0 else 1 end + 

case when col3 = '' then 0 when col3 is null then 0 else 1 end +

case when col4 = '' then 0 when col4 is null then 0 else 1 end ) 'COUNT',

      ConcateColumn = STUFF(

                          COALESCE('* ' + RTRIM(col1),'') 

                         +COALESCE('* ' + RTRIM(col2),'') 

                         +COALESCE('* ' + RTRIM(col4),'') 

                         +COALESCE('* ' + RTRIM(col3),'') 

                   , 1, 2, '') from temp1

                   group by col1,col2,col3,col4