Multiple lines in a single column for creating a VIEW

Question

I have this kind of data :

Client Number        Name of the direction
11                   John
11                   JIM
11                   Isabel
12                   Paul
12                   Batman
13                   Flash

I want to create a view like this :

CLIENT NUMBER        NAMES
11                   John, Jim, Isabel
12                   Paul, Batman
13                   Flash

I can have one or several names for the same client number.

How can I create a View with the different names in the same column ?

I already made a view but I Can't figure how to concat the names ?

Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Charlieface, Apr 21 '21 at 13:28

Annamalai D · Answer 1 · 2021-04-21T12:57:59.693

0

You can use STRING_AGG().

CREATE VIEW your_view_name AS  -- replace your view name
SELECT [Client Number],STRING_AGG([Name of the direction],', ') AS [NAME] FROM your_table_name -- replace you table name
GROUP BY [Client Number]

edited Apr 21 '21 at 12:57

answered Apr 21 '21 at 12:51

Annamalai D

859
1
7
21

score 0 · Answer 2 · answered Apr 21 '21 at 13:08

For SQL Server versions prior to 2017, you could also use FOR XML PATH.

CREATE VIEW view_name AS
SELECT DISTINCT T2.clientNumber, 
    SUBSTRING(
        (
            SELECT ','+ T1.Names  AS [text()]
            FROM table_Name T1
            WHERE T1.clientNumber = T2.clientNumber
            ORDER BY T1.clientNumber
            FOR XML PATH ('')
        ), 2, 1000) AS [NameList]
FROM table_Name T2

Multiple lines in a single column for creating a VIEW

2 Answers2