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;