追記 - sqlserver 文字列結合 null




SQL Serverで複数の行のテキストを1つのテキスト文字列に連結する方法は? (20)

3つの行で名前を保持するデータベース表を考えてみましょう。

Peter
Paul
Mary

これをPeter, Paul, Mary単一の文字列に変換する簡単な方法はありますか?


この答えは、ORDER BY句が存在するときに予期しない結果を返すことがあります。 一貫性のある結果を得るには、他の回答に詳述されているFOR XML PATHメソッドの1つを使用します。

COALESCE使用する:

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

ちょっとした説明(この答えは比較的規則的な見方をしているようです):

  • 合体は実際には2つのことを成し遂げる有益なチートです:

1) @Namesを空の文字列値で初期化する必要はありません。

2)最後に余分なセパレータを取り除く必要はありません。

  • 上記の解決策は、行にNULLの名前値がある場合に不正な結果を返します( NULLがある場合、 NULLはその行の後に@Names NULLを作成し、次の行は空の文字列として再び開始されます)。 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

あなたが望む動作に応じて(最初のオプションは単にNULLをフィルタリングするだけです。)、2番目のオプションはそれらをマーカーメッセージで置き換えます[適切なものはN / Aに置き換えてください])。


SQL Server 2017+およびSQL Azure:STRING_AGG

次のバージョンのSQL Serverから、最終的に変数や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;

MS SQL ServerのXML data()コマンドでまだ表示されていない方法の1つは次のとおりです。

FNameという1つの列を持つ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

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 11gリリース2では、LISTAGG機能がサポートされています。 ドキュメントはhere

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文字を超えないように独自の関数をロールバックする必要があります。


Oracle DBの場合は、次の質問を参照してください。ストアド・プロシージャを作成せずにOracleで複数の行を1つに連結する方法

最善の答えは、Oracle 11g Release 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より前のバージョンでは、hgmnzのようにarray_agg()を使うことができます


Postgres配列は素晴らしいです。 例:

いくつかのテストデータを作成する:

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以降、それはさらに簡単です。


SQL Server 2017またはAzureの場合は、 Mathieu Rendaの回答を参照してください。

私は1対多の関係で2つのテーブルを結合しようとしていたときに同様の問題が発生しました。 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を使用して最初のものをスキップして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


nullを処理する場合は、where句を追加するか、最初の句の周りに別のCOALESCEを追加することでそれを実行できます。

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

COALESCEを使用する - 詳細はこちらから

例:

102

103

104

次に、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

あなたは最終結果を保持する変数を作成し、それを選択する必要があります。

最も簡単なソリューション

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

このメソッドは、NPATH関数を使用する場合にのみTeradata Asterデータベースに適用されます。

再び、我々はテーブルの学生がいる

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

NPATHを使用すると、単なるSELECTです。

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

結果:

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

これも便利です

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

他の答えでは、答えを読んでいる人は、車両や学生などの特定のドメインテーブルを認識している必要があります。 ソリューションをテストするには、テーブルを作成してデータを入力する必要があります。

以下は、SQL Serverの "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 

再帰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

私は本当にダナの答えの elegancyが好きだった。 ちょうどそれを完全にしたいと思った。

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

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

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

私は通常、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

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

これは冒頭に迷いのカンマを置きます。

ただし、他の列やCSV子テーブルが必要な場合は、これをスカラーユーザー定義フィールド(UDF)でラップする必要があります。

SELECT節でも相関関係のあるサブクエリとしてXMLパスを使用できます(ただし、Googleは自宅で仕事をしないため、仕事に戻るまで待つ必要があります:-)





group-concat