How to group 3 columns into one in SQL

Question

I have a table with three columns: myColumn, myColumn2 and myColumn3. Three columns may have a different meaning, but they are all the same.

I need to make the connection so that I combine all the elements in three columns and then group by

If I had one column, this would be done as follows:

select distinct (myColumn), COUNT(myColumn) as 'TotalF' from FormularioCorto where idEvento = @idEvento and myColumn <> '' group by myColumn 

Question: How do I make a TSQL statement that allows me to join three columns and then group by?

Edit: here is sample data I have a table that has 3 columns with the same type

 columna columnb columnc aab bab ccd 

I need to run tsql, which will combine 3 columns and a group to get the result, e.g.

 newcolumn count a 3 b 3 c 2 d 1 

Any ideas?

Thank you in advance

Answer 1

I think you mean concatenate, not JOIN. JOIN has a very specific meaning with SQL. Use + operator to combine string fields.

Answer 2

It's hard to say without any sample data and the desired result, but guess something like:

 SELECT MyCol as MyColumn, Count(*) as 'TotalF' FROM ( SELECT myColumn MyCol from FormularioCorto WHERE idEvento = @idEvento AND myColumn <> '' UNION ALL SELECT myColumn2 MyCol from FormularioCorto WHERE idEvento = @idEvento AND myColumn2 <> '' UNION ALL SELECT myColumn3 MyCol from FormularioCorto WHERE idEvento = @idEvento) AND myColumn3 <> '' ) A GROUP BY MyCol; 

Or just for example data and result:

 SELECT NewColumn, Count(*) as Count FROM ( SELECT columna as NewColumn FROM SomeTable UNION ALL SELECT columnb as NewColumn FROM SomeTable UNION ALL SELECT columnc as NewColumn FROM SomeTable ) A Group by NewColumn 

Answer 3

 CONCAT(`myColumn`, `myColumn2`, `myColumn3`) as joinedField 

The CONCAT () function concatenates fields and strings.

Info: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

Note. You should review your structure, although it will be very painfully slow as you add more entries.

Answer 4

 select cast(myColumn as nvarchar) + CAST(COUNT(myColumn)as nvarchar) as 'TotalF' from FormularioCorto where idEvento = @idEvento and myColumn <> '' group by myColumn 

Answer 5

If I understand your question correctly, you just need 2 columns (one of which identifies the identifier that you are counting, and the other is the counter for the identifier. If this is correct, just use the existing query for each column and combine the results.

For instance:

 SELECT myColumn AS Id, COUNT(myColumn) AS IdCount FROM FormularioCorto where idEvento = @idEvento and myColumn <> '' GROUP BY myColumn UNION SELECT myColumn2 AS Id, COUNT(myColumn2) AS IdCount FROM FormularioCorto where idEvento = @idEvento and myColumn2 <> '' GROUP BY myColumn2 

etc.

Answer 6

Try the following:

 SELECT myColumn1 + myColumn2 + myColumn3 AS AllThreeFields FROM FormularioCorto WHERE 1=1 GROUP BY myColumn1 + myColumn2 + myColumn3 

It will not be fast, but it should work. If you need to distinguish between data in columns with other characters, you can try the following:

 SELECT myColumn1 + ' ' + myColumn2 + ' ' + myColumn3 AS AllThreeFields FROM FormularioCorto WHERE 1=1 GROUP BY myColumn1 + myColumn2 + myColumn3 

If the column types do not match (you said they were), you will need to apply them to the same type.