복붙노트

[SQL] 어떻게 SQL 서버에서 하나의 텍스트 문자열에 여러 행의 텍스트를 연결하는?

SQL

어떻게 SQL 서버에서 하나의 텍스트 문자열에 여러 행의 텍스트를 연결하는?

세 개의 행으로 이름을 들고 데이터베이스 테이블을 고려 :

Peter
Paul
Mary

피터, 폴, 메리의 단일 문자열로 이것을 설정하는 쉬운 방법이 있나요?

해결법

  1. ==============================

    1.당신은 SQL 서버 2017 또는 푸른에있는 경우, 마티유 RENDA 대답을 참조하십시오.

    당신은 SQL 서버 2017 또는 푸른에있는 경우, 마티유 RENDA 대답을 참조하십시오.

    나는 일대 다 관계로 두 개의 테이블을 조인하려고했던 비슷한 문제가 있었다. SQL 2005에서 그 XML의 PATH 방법은 아주 쉽게 행의 연결을 처리 할 수 ​​있습니다 발견했다.

    테이블라는 학생이있는 경우

    SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

    I이었다 예상 결과 :

    SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

    나는 다음과 같은 T-SQL을 사용 :

    SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

    당신이 처음에 쉼표를 CONCAT하고 하위 쿼리를 수행 할 필요가 없습니다 그래서 첫 번째를 건너 하위 문자열 사용할 수있는 경우는보다 컴팩트 한 방법으로 같은 일을 할 수 있습니다 :

    SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2
  2. ==============================

    2.사용 COALESCE :

    사용 COALESCE :

    DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

    그냥 몇 가지 설명 (이 답변은 상대적으로 정기적 인 의견을 얻을 것으로 보인다 이후) :

    빈 문자열 값으로 @names를 초기화 1) 필요가 없습니다.

    2) 필요가 끝에 추가 세퍼레이터를 벗겨 없음.

    DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

    또는:

    DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

    당신이 원하는 행동에 따라 (첫 번째 옵션은 단지 두 번째 옵션은 [당신을 위해 적합한 무엇 이건 'N / A'를 대체] 마커 메시지와 함께 목록에 유지, 널 (null)을 필터링).

  3. ==============================

    3.SQL 서버의 다음 버전을 시작으로, 우리는 마침내 어떤 변수 나 XML 마법에 의존하지 않고 행을 통해 연결할 수 있습니다.

    SQL 서버의 다음 버전을 시작으로, 우리는 마침내 어떤 변수 나 XML 마법에 의존하지 않고 행을 통해 연결할 수 있습니다.

    STRING_AGG (Transact-SQL)를 참조하십시오

    그룹화없이

    SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

    그룹화와 :

    SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

    그룹화 및 하위 분류로

    SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;
  4. ==============================

    4.아직 MS SQL Server의 XML 데이터 () 명령을 통해 도시하지 않은 한 가지 방법은 다음과 같습니다

    아직 MS SQL Server의 XML 데이터 () 명령을 통해 도시하지 않은 한 가지 방법은 다음과 같습니다

    , FNAME라는 하나 개의 컬럼은 NameList라는 테이블을 가정

    SELECT FName + ', ' AS 'data()' 
FROM NameList 
FOR XML PATH('')

    보고:

    "Peter, Paul, Mary, "

    만 추가 쉼표 처리해야합니다.

    편집 : NReilingh의 코멘트 @에서 채택, 당신은 뒤에 쉼표를 제거하려면 다음 방법을 사용할 수 있습니다. 같은 테이블 및 열 이름을 가정 :

    STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
  5. ==============================

    5.

    SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

    당신은에 대한 JSON 구문을 사용할 수 있습니다

    즉

    SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

    그리고 결과가 될 것입니다

    Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

    이것은 잘못된 XML 문자가 포함 된 경우에도 데이터를 작동합니다

    ,이 _ \ ", {\} '으로 탈출 할 것'"}, { "_": " '": \ "당신의 데이터가 포함 된 경우'" ""}, { "_ '때문에 안전합니다

    당신은 어떤 문자열 구분 기호 ','대체 할 수있다

    당신은 새로운 STRING_AGG 기능을 사용할 수 있습니다

  6. ==============================

    6.MySQL의에서 여러 행의 값을 연결할 수있는 기능, GROUP_CONCAT ()가있다. 예:

    MySQL의에서 여러 행의 값을 연결할 수있는 기능, GROUP_CONCAT ()가있다. 예:

    SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a
  7. ==============================

    7.사용 COALESCE - 여기에서 자세히 알아보기

    사용 COALESCE - 여기에서 자세히 알아보기

    예를 들어 :

    다음 SQL Server 코드 아래에 쓰기

    Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

    출력은 다음과 같습니다

    102,103,104
  8. ==============================

    8.포스트 그레스 배열은 굉장하다. 예:

    포스트 그레스 배열은 굉장하다. 예:

    테스트 데이터를 생성합니다 :

    postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

    배열을 집계 :

    test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

    쉼표로 구분 된 문자열로 배열 변환 :

    test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

    끝난

    PostgreSQL의 9.0 이후로는 더 쉽게입니다.

  9. ==============================

    9.오라클 11g 릴리즈 2는 LISTAGG 기능을 지원합니다. 여기에 문서.

    오라클 11g 릴리즈 2는 LISTAGG 기능을 지원합니다. 여기에 문서.

    COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

    이상 4000 자가는 결과 문자열의 가능성이있는 경우이 기능을 구현주의하십시오. 그것은 예외가 발생합니다. 그 후, 그렇다면 당신도 처리 할 수있는 예외를 필요하거나 자신의 기능을 굴려 그 이상의 4000 문자를가는 결합 된 문자열을 방지 할 수 있습니다.

  10. ==============================

    10.SQL 서버 2005 이상에서는 행을 연결하는 아래의 쿼리를 사용합니다.

    SQL 서버 2005 이상에서는 행을 연결하는 아래의 쿼리를 사용합니다.

    DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t
  11. ==============================

    11.여기 구문에서 추측 해요,하지만 더 많거나 적은 그래서 나는, 집에서 SQL 서버에 액세스 할 수 없습니다 :

    여기 구문에서 추측 해요,하지만 더 많거나 적은 그래서 나는, 집에서 SQL 서버에 액세스 할 수 없습니다 :

    DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names
  12. ==============================

    12.재귀 CTE 솔루션이 제안되었지만, 코드가 제공되지 않았다. 아래의 코드는 재귀 CTE의 일례이다. 결과가 질문을 일치하지만 난 당신이 정말 행 그룹, 테이블의 모든 행에이 일을한다고 가정으로 주 데이터는 매우, 주어진 설명과 일치하지 않습니다. 테이블이 독자들에게 연습 문제로 남겨에서이를 변경하면 모든 행하였습니다.

    재귀 CTE 솔루션이 제안되었지만, 코드가 제공되지 않았다. 아래의 코드는 재귀 CTE의 일례이다. 결과가 질문을 일치하지만 난 당신이 정말 행 그룹, 테이블의 모든 행에이 일을한다고 가정으로 주 데이터는 매우, 주어진 설명과 일치하지 않습니다. 테이블이 독자들에게 연습 문제로 남겨에서이를 변경하면 모든 행하였습니다.

    ;WITH basetable AS (
    SELECT
        id,
        CAST(name AS VARCHAR(MAX)) name, 
        ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw, 
        COUNT(*) OVER (Partition BY id) recs 
    FROM (VALUES
        (1, 'Johnny', 1),
        (1, 'M', 2), 
        (2, 'Bill', 1),
        (2, 'S.', 4),
        (2, 'Preston', 5),
        (2, 'Esq.', 6),
        (3, 'Ted', 1),
        (3, 'Theodore', 2),
        (3, 'Logan', 3),
        (4, 'Peter', 1),
        (4, 'Paul', 2),
        (4, 'Mary', 3)
    ) g (id, name, seq)
),
rCTE AS (
    SELECT recs, id, name, rw
    FROM basetable
    WHERE rw = 1

    UNION ALL

    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
    FROM basetable b
    INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
  13. ==============================

    13.당신과 같이, 최종 결과를 저장하고 그것으로 선택하는 변수를 작성해야합니다.

    당신과 같이, 최종 결과를 저장하고 그것으로 선택하는 변수를 작성해야합니다.

    DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;
  14. ==============================

    14.PostgreSQL의 9.0 시작이 매우 간단합니다 :

    PostgreSQL의 9.0 시작이 매우 간단합니다 :

    select string_agg(name, ',') 
from names;

    사용할 수있는 9.0 array_agg (이전 버전)에 의해 도시 된 바와 같이 hgmnz

  15. ==============================

    15.SQL 서버 vNext에서 이것은 STRING_AGG 기능을 내장되며, 여기에 더 그것에 대해 읽기 : https://msdn.microsoft.com/en-us/library/mt790580.aspx

    SQL 서버 vNext에서 이것은 STRING_AGG 기능을 내장되며, 여기에 더 그것에 대해 읽기 : https://msdn.microsoft.com/en-us/library/mt790580.aspx

  16. ==============================

    16.XML을 사용하여 쉼표로 구분 행을 받고 나를 도왔다. 여분의 쉼표를 위해 우리는 SQL 서버의 대체 기능을 사용할 수 있습니다. 콤마 대신 상기 AS의 사용의 추가의 데이터를 () 이후 다음 기록 구문 콤마로 대체 될 수 공백으로 행을 연결한다.

    XML을 사용하여 쉼표로 구분 행을 받고 나를 도왔다. 여분의 쉼표를 위해 우리는 SQL 서버의 대체 기능을 사용할 수 있습니다. 콤마 대신 상기 AS의 사용의 추가의 데이터를 () 이후 다음 기록 구문 콤마로 대체 될 수 공백으로 행을 연결한다.

    REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ')
  17. ==============================

    17.별도의 쉼표가있는 즉시 사용 가능한 솔루션 :

    별도의 쉼표가있는 즉시 사용 가능한 솔루션 :

    select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

    빈 목록은 NULL 값 발생합니다. 일반적으로 당신은 테이블 열 또는 프로그램 변수에 목록을 삽입합니다 : 당신의 필요에 255 최대 길이를 조정합니다.

    (디와 카르와 옌스 프랜드 슨은 좋은 대답하지만, 필요 개선을 제공했다.)

  18. ==============================

    18.

    SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

    다음은 샘플입니다 :

    DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
  19. ==============================

    19.

    DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

    이 풋 시작 부분에 부유 쉼표.

    다른 열을해야하는 경우, 또는 CSV에 자식 테이블 당신은 스칼라 사용자 정의 필드 (UDF)이 포장해야합니다.

    당신은 너무 SELECT 절에 상관 하위 쿼리로 XML 경로를 사용할 수 있습니다 (하지만 구글이 집에서 작업 물건을하지 않기 때문에 나는 직장으로 돌아갈 때까지 기다려야 할 것입니다 :-)

  20. ==============================

    20.다른 답변으로 대답을 읽는 사람은 차량이나 학생 같은 특정 도메인 테이블을 알고 있어야합니다. 테이블 생성 및 솔루션을 테스트 데이터로 채워야합니다.

    다른 답변으로 대답을 읽는 사람은 차량이나 학생 같은 특정 도메인 테이블을 알고 있어야합니다. 테이블 생성 및 솔루션을 테스트 데이터로 채워야합니다.

    다음은 SQL 서버 "INFORMATION_SCHEMA.COLUMNS"테이블을 사용하는 예입니다. 이 용액을 사용하여, 어떤 테이블 작성 또는 데이터가 추가 될 필요가 없다. 이 예는 데이터베이스 내의 모든 테이블 항목 콤마로 구분을 생성한다.

    SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
  21. ==============================

    21.어떻게 여러 행이 저장 프로 시저를 생성하지 않고 오라클에 하나에 연결될 수 있습니다 오라클 DB를 들어,이 질문을보고?

    어떻게 여러 행이 저장 프로 시저를 생성하지 않고 오라클에 하나에 연결될 수 있습니다 오라클 DB를 들어,이 질문을보고?

    가장 좋은 응답이 나타납니다 나중에 오라클 11g 릴리즈 2에서 사용할 수있는 내장 LISTAGG () 함수를 사용하여, @Emmanuel으로합니다.

    SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

    user762952 지적, 오라클의 문서 http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php에 따라 @ 같이 WM_CONCAT () 함수는 옵션입니다. 그것은 안정을 보이지만, 오라클은 명시 적으로 자신의 위험에 사용하는 모든 응용 프로그램의 SQL 위해 그것을 사용에 대한 권장합니다.

    다른 것보다, 당신은 당신의 자신의 함수를 작성해야합니다; 오라클 문서는 위의 그 작업을 수행하는 방법에 대한 안내가 있습니다.

  22. ==============================

    22.정말 다나의 대답의 정밀함을 좋아했다. 그냥이 완료 만들고 싶었다.

    정말 다나의 대답의 정밀함을 좋아했다. 그냥이 완료 만들고 싶었다.

    DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names 

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
  23. ==============================

    23.널 (null) 값을 방지하기 위해 당신은) (CONCAT을 사용할 수 있습니다

    널 (null) 값을 방지하기 위해 당신은) (CONCAT을 사용할 수 있습니다

    DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names
  24. ==============================

    24.이 답변은 작업을 서버에서 일부 권한이 필요합니다.

    이 답변은 작업을 서버에서 일부 권한이 필요합니다.

    어셈블리는 당신을 위해 좋은 옵션이다. 그것을 만드는 방법을 설명하는 사이트가 많이 있습니다. 나는 아주 잘 설명되어 생각 하나는 이것이다

    당신이 원하는 경우에, 나는 이미 어셈블리를 만든, 그리고 여기에 DLL을 다운로드 할 수 있습니다.

    당신이 그것을 다운로드하면, 당신은 당신의 SQL 서버에서 다음 스크립트를 실행해야합니다 :

    CREATE Assembly concat_assembly 
   AUTHORIZATION dbo 
   FROM '<PATH TO Concat.dll IN SERVER>' 
   WITH PERMISSION_SET = SAFE; 
GO 

CREATE AGGREGATE dbo.concat ( 

    @Value NVARCHAR(MAX) 
  , @Delimiter NVARCHAR(4000) 

) RETURNS NVARCHAR(MAX) 
EXTERNAL Name concat_assembly.[Concat.Concat]; 
GO  

sp_configure 'clr enabled', 1;
RECONFIGURE

    어셈블리에 대한 경로가 서버에 액세스 할 수 있음을 관찰한다. 성공적으로 모든 단계를 완료했기 때문에, 당신은 같은 기능을 사용할 수 있습니다 :

    SELECT dbo.Concat(field1, ',')
FROM Table1

    희망이 도움이!

  25. ==============================

    25.나는 보통 SQL Server의 CONCATENATE 문자열을 다음과 같이 사용을 선택합니다 :

    나는 보통 SQL Server의 CONCATENATE 문자열을 다음과 같이 사용을 선택합니다 :

    with lines as 
( 
  select 
    row_number() over(order by id) id, -- id is a line id
    line -- line of text.
  from
    source -- line source
), 
result_lines as 
( 
  select 
    id, 
    cast(line as nvarchar(max)) line 
  from 
    lines 
  where 
    id = 1 
  union all 
  select 
    l.id, 
    cast(r.line + N', ' + l.line as nvarchar(max))
  from 
    lines l 
    inner join 
    result_lines r 
    on 
      l.id = r.id + 1 
) 
select top 1 
  line
from
  result_lines
order by
  id desc
  26. ==============================

    26.당신이 널 (null)을 처리하려면 당신은 where 절을 추가하여 그것을 할 또는 첫 번째 주위에 다른 COALESCE를 추가 할 수 있습니다.

    당신이 널 (null)을 처리하려면 당신은 where 절을 추가하여 그것을 할 또는 첫 번째 주위에 다른 COALESCE를 추가 할 수 있습니다.

    DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
  27. ==============================

    27.예를 완료 MYSQL :

    예를 완료 MYSQL :

    우리는 많은 데이터의 수 사용자를 가지고 우리는 우리가 목록에있는 모든 사용자 Datas를 볼 수있는 출력을 갖고 싶어 :

    결과:

    ___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

    표 설정 :

    CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

    질문:

    SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
  28. ==============================

    28.오라클에서는 wm_concat입니다. 나는이 기능은 10g 릴리스 이상에서 사용할 수 있습니다 생각합니다.

    오라클에서는 wm_concat입니다. 나는이 기능은 10g 릴리스 이상에서 사용할 수 있습니다 생각합니다.

  29. ==============================

    29.이 역시 유용 할 수 있습니다

    이 역시 유용 할 수 있습니다

    create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

    보고

    Peter,Paul,Mary
  30. ==============================

    30.--sql 서버 2005 +

    --sql 서버 2005 +

    CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)      --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT 
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT 
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;

    31. from https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 는 SQL IN 절을 매개 변수화  (0) 2020.03.04
[SQL] SQL 서버 동적 PIVOT 쿼리?  (0) 2020.03.04
[SQL] 각 그룹의 마지막 레코드를 가져 - MySQL의  (0) 2020.03.04
[SQL] 각 GROUP BY 그룹에서 첫 번째 행을 선택?  (0) 2020.03.04
[SQL] 2005 마이크로 소프트 SQL 서버에서 GROUP_CONCAT MySQL의 기능을 시뮬레이션?  (0) 2020.03.04
,

티스토리툴바