How to Implement GROUP_CONCAT in SQL SERVER

Introduction

Imagine that we want to return a list of products from a store in a query only. Is it possible? For example, what returns the attribute 'products' as follows: video games, cd, dvd, chair.

In MySQL, it's simple, you just call the GROUP_CONCAT function.

But in SQL SERVER, it is a little more complicated. To do this, one must do the following:

Content

Consider the following table structure:

CREATE TABLE store(
id INTEGER,
name varchar(50)

)

CREATE TABLE product(
id INTEGER,
id_store INTEGER
name VARCHAR(10),
price FLOAT
)

Now, let's perform some inserts:

INSERT INTO store VALUES(1,'CARREFULL');

INSERT INTO product VALUES (1,1,'videogame',2.00);
INSERT INTO product VALUES (2,1,'cd',3.00);
INSERT INTO product VALUES (3,1,'dvd',4.00);
INSERT INTO product VALUES (4,1,'table',5.00);

Creating a temporary table (this may vary depending on the used SGBD) with product data.

SELECT product.name,
Values1 = CAST(NULL AS VARCHAR(8000))
INTO #teste
FROM product
WHERE product.id_store = '1'

Update to go through all the records from the temporary table.

/* Creating variables */
DECLARE

    @retorno VARCHAR(8000),

    @Values1 varchar(8000)

/* setting default value for the variable @Values1 */
SET @Values1 = ''

/* Performing an update on the temporary #teste table to scroll through all the records,
and thus be able to add the name of each variable product will @Values1 */

UPDATE #teste
    SET Values1 = nome,

    @Values1 =  @Values1  + name + ','

/* conditionally setting the value of the variable @retorno
as equivalent to the value of the variable @Values1 */
if     @Values1 = ''

    SET @retorno = '-'

else

    SET @retorno = @Values1

In query, we decided that the list of product names separated by commas, will be in the column 'product'.

select loja.*,products=@retorno from store WHERE id = 1;