Get the Count of Concatenating Column in sql
sql concatenate column values
how to concatenate two columns in sql with comma
sql concatenate string and int
sql append string to column value
how to concatenate two strings in sql server 2008
sql concatenate rows into string group by
i have Concatenating the multiple Columns and i want the count of how many columns are Concatenated
the query output and expected output are
select ConcateColumn = STUFF( COALESCE('* ' + RTRIM(col1),'') +COALESCE('* ' + RTRIM(col2),'') +COALESCE('* ' + RTRIM(col4),'') +COALESCE('* ' + RTRIM(col3),'') , 1, 2, '')
=================================== | 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 | | | ===============
------------------------ | 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
"SELECT with concatenation, CAST and COUNT", Is it possible to do a query which contains SELECT, concatenation, CAST and COUNT, SELECT COUNT(*) and so on but it didn't work either. the SQL. But if the translation error actually occurred when the data was being TABLE to verify the CHARACTERSET for your column(s), then intentionally Columns of data type int should be converted or type cast to string data type like VARCHAR to perform string concatenation. Click this link to view the demo in SQL Fiddle. Script: Tested in SQL Server 2012.
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
'' you can try to use
SQL, SQL Tutorial SQL CONCAT function is used to concatenate 2 or more strings Syntax for CONCATE() function in SQL, SELECT CONCATE (string1, string2) Column names in this table: Student_ID, First_name, Last_name, City and Marks COUNT(), SQL COUNT() function returns/counts the number of rows in a query. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL
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
SQL Server CONCAT() Function, SQL Server CONCAT() Function. ❮ SQL Server Functions. Example. Add two strings together: SELECT CONCAT('W3Schools I want to concatenate 4 columns which are from 3 different tables and get the count of concatenate. Table1: ID Name Country 1 Rick Jpn 2 Ken Chn 3 Tim Swz 4 Rick Jpn 5 Ken Chn < Table2: ID Curr Amt 1 Yen 344.55 2 Yun 225 3 Frn 459 4 Yen 344.55 5 Yun 225 Result:
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
MySQL, You've already found your answer, but here are the reasons: The reason you're getting duplicates is because you're joining tab1 with itself SQL CONCATENATE (appending strings to one another) String concatenation means to append one string to the end of another string. SQL allows us to concatenate strings but the syntax varies according to which database system you are using. Concatenation can be used to join strings from different sources including column values, literal strings, the output from user-defined functions or scalar sub-queries, etc.
In SQL, how do I count DISTINCT over multiple columns?, SELECT count(DISTINCT ColumnA, ColumnB) FROM TableX;. In other DB, you can concat the columns and then count on that concatenated String like below:. How to count the number of columns of the SQL table? Count the number of all columns of the "Employee" table select count (column_name) as Number from information_schema.columns where table_name= 'Employee'
[SQL Basic] How to work with String Functions in SQL —My SQL , SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name FROM customer; Let's count the length of the customer's full name (LENGTH) So first set the columns you want to view in the SELECT function: Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign to concatenate fields together. The limitation of this method is if any of the fields you are concatenating are NULL, the entire result is NULL. In SQL Server 2012 and later there is the CONCAT() function that replaces NULL with an empty string. Take a look at this tip to see how this new function works and how it can be beneficial in your code.
12.17.1 Aggregate (GROUP BY) Function Descriptions, COUNT(DISTINCT), Return the count of a number of different values SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE In standard SQL, you would have to do a concatenation of all expressions inside In MySQL, you can get the concatenated values of expression combinations. SELECT Countries = STUFF ( (. SELECT ',' + CountryName. FROM Application.Countries. FOR XML PATH ('') ), 1, 1, '') 2. Concatenate Rows Using COALESCE. You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher.
- Off the top of my head, you could do something using
CASEstatements and a subquery to achieve this. What have you already tried to calculate this?
- please note that the no of columns are dynamic
- if the column is empty i need count as 0 , but im getting as 1
- What do you mean by empty? Empty string
''? It works with the D-Shih's dbfiddle.uk/…
- check it in fiddle in last insert column
- INSERT INTO T VALUES ( NULL, '' , NULL , NULL );
- Well, an empty string is not the same as a NULL, of course it's counted.
sum, but you beat me to it... +1 :-)
- @ZoharPeled Yes Thanks
- please note that the columns may a string, just for understanding i gave it as numbers
- @RamapriyanC It can work if columns are string dbfiddle.uk/…
- If you want empty strings treated as NULLs, you must add
NULLIF, e.g. `RTRIM(NULLIF(col1),''))