شرح كيف لسَلسَلة النص من صفوف متعددة في سلسلة نصية واحدة في خادم SQL؟




انواع sql injection (24)

خذ بعين الاعتبار جدول قاعدة بيانات يحمل أسماء ، مع ثلاثة صفوف:

Peter
Paul
Mary

هل هناك طريقة سهلة لتحويل هذا إلى سلسلة واحدة من Peter, Paul, Mary ؟



إذا كنت تريد التعامل مع الأصفار ، يمكنك القيام بذلك عن طريق إضافة جملة أو إضافة تحويلة أخرى حول أول واحد.

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

في SQL Server 2005

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

في SQL Server 2016

يمكنك استخدام بناء جملة FOR 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   [email protected]
2   NULL
3   [email protected], [email protected]

سيعمل هذا حتى تحتوي بياناتك على أحرف XML غير صالحة

يكون "" "، {" ":" 'آمنًا لأنه إذا احتوت البيانات على ""} ، {" ": "، فسيتم هربها إلى"} ، {\ "_ \": \ "

يمكنك استبدال "،" بأي فاصل للسلسلة

وفي SQL Server 2017 ، قاعدة بيانات Azure SQL

يمكنك استخدام وظيفة STRING_AGG الجديدة


عادة ما أستخدم تحديد مثل هذا لسَلسَلة سلاسل في SQL Server:

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

بالنسبة لـ Oracle DBs ، راجع هذا السؤال: كيف يمكن ربط صفوف متعددة في واحد في Oracle بدون إنشاء إجراء مخزن؟

يبدو أن أفضل إجابة هي:Emanuel ، باستخدام الدالة LISTAGG () المدمجة ، والمتاحة في الإصدار 11g من Oracle 11 والإصدارات الأحدث.

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 () هي أيضا خيار. يبدو مستقرًا ، لكن Oracle توصي صراحة بعدم استخدامه لأي تطبيق SQL ، لذلك استخدمه على مسؤوليتك الخاصة.

بخلاف ذلك ، سيكون عليك كتابة الوظيفة الخاصة بك ؛ يحتوي مستند Oracle أعلاه على دليل حول كيفية القيام بذلك.


استخدم CONALCE - اعرف المزيد من هنا

على سبيل المثال:

102

103

104

ثم اكتب التعليمات البرمجية أدناه في خادم SQL ،

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

واجهت مشكلة مشابهة عندما كنت أحاول الانضمام إلى جدولين مع علاقات رأس بأطراف. في 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

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

هذا يضع الفاصلة الضالة في البداية.

ومع ذلك ، إذا كنت بحاجة إلى أعمدة أخرى ، أو إلى جدول CSV ، فستحتاج إلى لف هذا في حقل معرف مستخدم قياسي (UDF).

يمكنك استخدام مسار XML كطلب فرعي مترابط في جملة SELECT أيضًا (ولكن يجب علي الانتظار حتى أعود إلى العمل لأن Google لا تقوم بعمل أشياء في المنزل :-)


تحتاج إلى إنشاء متغير سيحمل النتيجة النهائية الخاصة بك وتحدد فيها ، مثل ذلك.

أسهل الحل

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

لتجنب القيم الخالية يمكنك استخدام CONCAT ()

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

يوجد في 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

هذا يمكن أن يكون مفيدا جدا

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

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

استخدم COALESCE :

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

فقط بعض التفسير (حيث يبدو أن هذه الإجابة تحصل على مشاهدات منتظمة نسبيًا):

  • Coalesce هو حقا مجرد غش مفيدة التي تحقق أمرين:

1) لا حاجة لتهيئة @Names بقيمة سلسلة فارغة.

2) لا حاجة لإلغاء فصل فاصل إضافي في النهاية.

  • الحل أعلاه يعطي نتائج غير صحيحة إذا كان الصف يحتوي على قيمة اسم NULL (إذا كان هناك NULL ، فإن 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 s ، فإن الخيار الثاني يحتفظ بها في القائمة مع رسالة علامة [استبدل 'N / A' بأي شيء مناسب لك]).


تم اقتراح حل 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

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

صفائح بوستجرس رائع. مثال:

إنشاء بعض بيانات الاختبار:

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 بل هو أسهل .


بدءًا من PostgreSQL 9.0 ، هذا بسيط للغاية:

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

في الإصدارات قبل 9.0 array_agg() يمكن استخدامها كما هو موضح من قبل hgmnz


أنا حقا أحب الأناقة للإجابة دانا . أردت فقط أن أكملها.

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

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

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

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

SQL Server 2017 و SQL Azure: STRING_AGG

بدءاً من الإصدار التالي من SQL Server ، يمكن أن نسلسل في النهاية عبر الصفوف دون الحاجة إلى اللجوء إلى أي متغير أو XML witchery.

بدون تجميع

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;

مع الإجابات الأخرى ، يجب أن يكون الشخص الذي يقرأ الإجابة على علم بجدول نطاق معين مثل السيارة أو الطالب. يجب إنشاء الجدول وملء البيانات لاختبار حل.

أدناه مثال يستخدم الجدول "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 

في Oracle ، يكون wm_concat . أعتقد أن هذه الوظيفة متوفرة في إصدار 10g وأعلى.


ساعدني استخدام XML في الحصول على صفوف مفصولة بفواصل. للفاصلة الإضافية يمكننا استخدام وظيفة الاستبدال من SQL Server. بدلاً من إضافة فاصلة ، سيستخدم استخدام AS 'data () الصفوف التي تحتوي على مسافات ، والتي يمكن استبدالها لاحقًا بفواصل كنص مكتوب أدناه.

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




group-concat