합치기 - mysql 여러 행 을 한줄 로




SQL 서버에서 여러 행의 텍스트를 단일 텍스트 문자열로 연결하는 방법? (20)

다음 세 행을 가진 이름을 가진 데이터베이스 테이블을 고려하십시오.

Peter
Paul
Mary

이것을 Peter, Paul, Mary 의 한 줄로 바꾸는 쉬운 방법이 있습니까?


이 응답은 ORDER BY 절이있을 때 예기치 않은 결과를 반환 할 수 있습니다. 일관된 결과를 얻으려면 다른 대답에 설명 된 FOR XML PATH 방법 중 하나를 사용하십시오.

COALESCE 사용 :

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

몇 가지 설명 (이 답변은 상대적으로 일정한보기를 얻은 것 같습니다) :

  • Coalesce는 실제로 두 가지를 수행하는 유용한 치트입니다.

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

2) 끝에 별도의 분리기를 벗을 필요가 없습니다.

  • 위의 솔루션은 행에 NULL Name 값이있는 경우 잘못된 결과를 제공합니다 ( NULL@Names NULL@Names 를 해당 행 다음에 NULL로 만들고 다음 행을 빈 문자열로 다시 시작합니다. 두 가지 솔루션 :
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

원하는 동작에 따라 (첫 번째 옵션은 NULL을 필터링 합니다 . 두 번째 옵션은 마커 메시지와 함께 목록에 'N / A'를 대치합니다.)).


SQL Server 2017 이상 및 SQL Azure : STRING_AGG

다음 버전의 SQL Server부터는 변수 나 XML witchery를 사용하지 않고도 여러 행을 연결할 수 있습니다.

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;

MS SQL Server의 XML data() 명령을 통해 아직 표시되지 않은 한 가지 방법은 다음과 같습니다.

NameList라는 테이블을 FName이라는 한 열로 가정합니다.

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

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

Oracle DB의 경우 다음 질문을 참조하십시오 . 저장 프로 시저를 만들지 않고 Oracle에서 여러 행을 어떻게 하나로 연결할 수 있습니까?

가장 좋은 대답은 오라클 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에 대해이를 사용하지 않을 것을 명시 적으로 권고합니다. 따라서 위험 부담은 사용자가 부담해야합니다.

그것 이외에, 당신은 당신 자신의 함수를 써야 할 것이다; 위의 Oracle 문서에는이를 수행하는 방법에 대한 지침이 있습니다.


PostgreSQL 9.0부터는 매우 간단합니다.

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

9.0 이전의 버전에서는 array_agg() 표시된 것처럼 array_agg() 사용할 수 있습니다.


SQL Server 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

SQL Server 2017 또는 Azure를 사용하는 경우 Mathieu Renda 대답을 참조하십시오.

일대 다 관계가있는 두 테이블을 조인 할 때 비슷한 문제가있었습니다. SQL 2005에서 나는 XML PATH 메서드가 행들의 연결을 매우 쉽게 처리 할 수 ​​있다는 것을 발견했다.

STUDENTS 라는 테이블이 있다면

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

예상 한 결과 :

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]

첫 번째 항목을 쉼표로 연결하고 substring 을 사용하여 첫 번째 항목을 건너 뛰면 하위 쿼리를 수행 할 필요가 없으므로 동일한 작업을보다 간단하게 수행 할 수 있습니다.

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

XML을 사용하면 행을 쉼표로 구분하는 데 도움이되었습니다. 여분의 쉼표로 SQL Server의 replace 함수를 사용할 수 있습니다. 쉼표를 추가하는 대신 AS 'data ()'를 사용하여 행을 공백으로 연결합니다. 공백은 나중에 쉼표로 바꿀 수 있습니다.

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

null 값을 피하기 위해 CONCAT ()

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

나는 일반적으로 SQL Server에서 문자열을 연결하기 위해 다음과 같이 select를 사용합니다.

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

널 (null)을 처리하려는 경우, where 절을 추가하거나 첫 x 째 절 주위에 다른 COALESCE를 추가하여 널 (null)을 처리 할 수 ​​있습니다.

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

오라클에서는 wm_concat 입니다. 이 기능은 10g 이상에서 사용할 수 있습니다.


이 대답은 서버 작동에 대한 몇 가지 특권이 필요합니다.

Assemblies 는 당신에게 좋은 옵션입니다. 사이트를 만드는 방법을 설명하는 사이트가 많이 있습니다. 제가 생각하기에 아주 잘 설명되어있는 것은 이것입니다.

원하는 경우 이미 어셈블리를 만들었으므로 here 에서 DLL을 다운로드 할 수 here .

다운로드가 완료되면 SQL Server에서 다음 스크립트를 실행해야합니다.

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

희망이 도움이 !!!


이것도 유용 할 수 있습니다.

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

재귀 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

최종 결과를 보유 할 변수를 생성해야합니다.

가장 쉬운 솔루션

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

추가 쉼표없이 바로 사용할 수있는 솔루션 :

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

빈 목록은 NULL 값이됩니다. 일반적으로 테이블 열 또는 프로그램 변수에 목록을 삽입합니다. 필요에 따라 최대 길이를 255로 조정하십시오.

(Diwakar와 Jens Frandsen은 좋은 대답을했지만 개선이 필요하다.)


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

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

이것은 시작에 빗나간 쉼표를 넣습니다.

그러나 다른 열이 필요하거나 자식 테이블을 CSV하려면 스칼라 사용자 정의 필드 (UDF)로이 테이블을 래핑해야합니다.

SELECT 절의 상관 된 하위 쿼리로 XML 경로를 사용할 수도 있습니다 (하지만 Google은 집에서 일을하지 않기 때문에 다시 돌아올 때까지 기다려야 할 것입니다 :-)





group-concat