T-SQL: 改行を挟んで複数行の文字列を結合する

複数行の文字列を結合してひとつの文字列にする

1 対 n となる親子関係のあるテーブルがあって、親テーブルの結果と共に、子テーブルの文字列を結合して、ひとつの文字列として返したいようなケースありますよね。

文字列を結合する際には、カンマやスペース等の区切り文字を入れることが多いかと思います。

今回は区切り文字として改行を挿入して文字列を結合する方法をご紹介します。

改行を挟んで文字列を結合する方法

例えば、次のような親子関係の Company テーブルと CompanyBranch テーブルがあります。

CREATE TABLE Company (
   CompanyID INT NOT NULL PRIMARY KEY,
   CompanyCode VARCHAR(50) NOT NULL,
   CompanyName VARCHAR(50) NOT NULL
);

INSERT INTO Company
  (CompanyID, CompanyCode, CompanyName)
 VALUES
  (1, 'AAA', 'AAA Company'),
  (2, 'BBB', 'BBB Company'),
  (3, 'CCC', 'CCC Company');

 CREATE TABLE CompanyBranch (
   CompanyBranchID INT NOT NULL PRIMARY KEY,
   CompanyID INT NOT NULL,
   BranchCode VARCHAR(50) NOT NULL,
   BranchName VARCHAR(50) NOT NULL,
   CONSTRAINT FK_CompanyBranch FOREIGN KEY (CompanyID) REFERENCES Company(CompanyID)
);

INSERT INTO CompanyBranch
  (CompanyBranchID, CompanyID, BranchCode, BranchName)
 VALUES
  (1, 1, 'LA', 'LA Branch'),
  (2, 1, 'NY', 'NY Branch'),
  (3, 1, 'SF', 'SF Branch'),
  (4, 2, 'HW', 'HW Branch'),
  (5, 3, 'SD', 'SD Branch'),
  (6, 3, 'FL', 'FL Branch');

Company テーブルの情報に加えて、CompanyBranch の情報を BranchList カラムとして [BranchCode]: [Branch Name] を改行文字でつなげて取得してみましょう。

やり方はいろいろあると思いますが、例えばこんな感じでできると思います。

SELECT	CompanyID,
	CompanyCode,
	CompanyName,
	REPLACE(
	    STUFF((SELECT '|||' + CB.BranchCode + ': ' + CB.BranchName
		   FROM		CompanyBranch AS CB WITH (NOLOCK)
		   WHERE	CB.CompanyID = C.CompanyID
		   ORDER BY	CB.BranchCode
		   FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, ''),
	'|||', CHAR(13) + CHAR(10)) AS BranchList
FROM	Company	AS C;