Join column data online - SQL Server

Asked

Viewed 597 times

0

I have the following situation:

Usuario_A     telefone1
Usuario_A     telefone2
Usuario_A     telefone3

How can I make so that when I make a select, I have the following output:

+-----------+----------------------------------+
| Usuario_A |  telefone1, telefone2, telefone3 |
+-----------+----------------------------------+

In Mysql I know I have the function GROUP_CONCAT, more to SQL Server did not identify something that can help me to solve.

sql sql-server

  • It would be interesting to put a more concrete example with the table structure, example data in the table and the expected output values. The group_concat joins the row data in column and in the title of your question is the opposite of this. If you want to use the Sqlfiddle to put the example that would work in Mysql

  • If the SQL Server version is 2017 (or newer), you can use STRING_AGG

  • This answers your question? How to concatenate lines?

2 answers

0

Try this:

SELECT Usuario, Telefone1 [TELEFONE] FROM tbUsuario
UNION ALL
SELECT Usuario, Telefone2 [TELEFONE] FROM tbUsuario
UNION ALL
SELECT Usuario, Telefone3 [TELEFONE] FROM tbUsuario

0

You can use the STUFF () with FOR XML PATH

CREATE TABLE T(
  [User] VARCHAR(45),
  Phone VARCHAR(45)
);

INSERT INTO T VALUES
('Usuario_A', 'telefone1'),
('Usuario_A', 'telefone2'),
('Usuario_A', 'telefone3'),
('Usuario_B', 'telefone4');

SELECT T1.[User],
       STUFF(
              (
                SELECT ',' + T2.Phone
                FROM T T2
                WHERE T2.[User] = T1.[User]
                FOR XML PATH('')
              ), 1, 1, ''
            ) Phones
FROM T T1
GROUP BY T1.[User];

Returns:

+-----------+-------------------------------+
|   User    |            Phones             |
+-----------+-------------------------------+
| Usuario_A | telefone1,telefone2,telefone3 |
| Usuario_B | telefone4                     |
+-----------+-------------------------------+

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.