Amit’s Blog…

The other day when I was helping my friend with an assignment I came across a very interesting query. Most of us might have used the SQL Concat function to concatenate from two columns in a row. That is pretty easy for eg.

SELECT CONCAT(firstname, ‘ ‘, secondname) FROM profiles;


The above SQL Statement is very easy and self explanatory.

Now, how would you write a query (or queries) where you want to concatenate elements from the same column from all the rows that have been selected? For example if you would want a list all the usernames in the table separated by a comma:

SET @usernames = “”;
--Initialize the variable usernames with an empty string.
SELECT @usernames := CONCAT(@usernames, “, “, username) FROM profiles;
-- Here all the usernames are concatenated to the usernames variable.
-- This is not our output as this will give us multiple rows, with the last row having all the usernames.
SET @usernames = SUBSTRING(@ids FROM 2);
-- Cleaning up the list by removing the comma at the beginning.
SELECT @usernames;


The above set of queries will get you all the usernames separated by comma.

This also solved one of my other problems that I was not able to solve earlier with mySQL. How would you write a query where you could have a column with Serial Number for the records? If you take a look at the above solution, this is very easy.

SET @sno = 0;
SELECT @sno := @sno+1 as SNO, username FROM profiles;


This will give you a list of usernames with Serial Numbers as a separate column.

Hope it was worthwhile reading the above .