sql - एसक्यूएल सर्वर में एक पंक्ति पाठ स्ट्रिंग में एकाधिक पंक्तियों से टेक्स्ट को कैसे सम्मिलित करें?




sql-server csv string-concatenation group-concat (25)

तीन पंक्तियों के साथ, डेटाबेस टेबल होल्डिंग नामों पर विचार करें:

Peter
Paul
Mary

क्या Peter, Paul, Mary की एक स्ट्रिंग में इसे बदलने का कोई आसान तरीका है?


Answers

जब मैं दो से अधिक रिश्तों के साथ दो तालिकाओं में शामिल होने की कोशिश कर रहा था तो मुझे एक ही समस्या थी। एसक्यूएल 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

COALESCE का उपयोग करें - यहां से और जानें

एक उदाहरण के लिए:

102

103

104

फिर एसक्यूएल सर्वर में कोड नीचे लिखें,

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 सर्वर तक पहुंच नहीं है, इसलिए मुझे यहां वाक्यविन्यास पर अनुमान है, लेकिन यह कम या कम है:

DECLARE @names VARCHAR(500)

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

मैं आमतौर पर SQL सर्वर में स्ट्रिंग को जोड़ने के लिए इस तरह का चयन करता हूं:

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(MAX)
SET @names = ''

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

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

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

यह शुरुआत में भटकना कॉमा डालता है।

हालांकि, अगर आपको अन्य कॉलम की आवश्यकता है, या सीएसवी को एक बाल तालिका में आपको स्केलर उपयोगकर्ता परिभाषित फ़ील्ड (यूडीएफ) में लपेटने की आवश्यकता है।

आप एक्सएमएल पथ को SELECT क्लॉज में एक सहसंबंधित सबक्वायरी के रूप में भी उपयोग कर सकते हैं (लेकिन मुझे तब तक इंतजार करना पड़ेगा जब तक कि मैं काम पर वापस नहीं जाता क्योंकि Google घर पर काम नहीं करता है :-)


ओरेकल 11 जी रिलीज 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 वर्णों से अधिक होने से रोकती है।


MySQL पूर्ण उदाहरण:

हमारे पास ऐसे उपयोगकर्ता हैं जिनके पास कई डेटा हो सकते हैं और हम एक आउटपुट चाहते हैं, जहां हम एक सूची में सभी उपयोगकर्ता डेटा देख सकते हैं:

परिणाम:

___________________________
| 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

अन्य उत्तरों के साथ, उत्तर पढ़ने वाले व्यक्ति को वाहन या छात्र जैसे विशिष्ट डोमेन तालिका से अवगत होना चाहिए। एक समाधान का परीक्षण करने के लिए तालिका को डेटा के साथ बनाया और पॉप्युलेट किया जाना चाहिए।

नीचे एक उदाहरण है जो 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 

ओरेकल डीबी के लिए, यह प्रश्न देखें: संग्रहीत प्रक्रिया के बिना ओरेकल में एकाधिक पंक्तियों को कैसे समेकित किया जा सकता है?

ओरेकल 11 जी रिलीज 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 () फ़ंक्शन भी एक विकल्प है। यह स्थिर लगता है, लेकिन ओरेकल स्पष्ट रूप से किसी भी अनुप्रयोग एसक्यूएल के लिए इसका उपयोग करने के खिलाफ सिफारिश करता है, इसलिए अपने जोखिम पर उपयोग करें।

इसके अलावा, आपको अपना खुद का कार्य लिखना होगा; उपरोक्त ओरेकल दस्तावेज़ में यह कैसे करना है इस पर एक गाइड है।


- एसक्यूएल सर्वर 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)
;

शून्य मानों से बचने के लिए आप CONCAT () का उपयोग कर सकते हैं

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

एसक्यूएल सर्वर 2017 और एसक्यूएल एज़ूर: STRING_AGG

SQL सर्वर के अगले संस्करण से शुरू होने पर, हम आखिरकार पंक्तियों में किसी भी चर या एक्सएमएल 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 सर्वर के प्रतिस्थापन फ़ंक्शन का उपयोग कर सकते हैं। अल्पविराम जोड़ने के बजाय, एएस 'डेटा ()' का उपयोग रिक्त स्थान के साथ पंक्तियों को जोड़ देगा, जिसे बाद में नीचे लिखा गया वाक्यविन्यास के रूप में अल्पविरामों के साथ प्रतिस्थापित किया जा सकता है।

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

यदि आप नल से निपटना चाहते हैं तो आप इसे कहां जोड़कर कर सकते हैं या पहले के आस-पास एक और COALESCE जोड़ सकते हैं।

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

एक पुनरावर्ती सीटीई समाधान का सुझाव दिया गया था, लेकिन कोई कोड प्रदान नहीं किया गया था। नीचे दिया गया कोड एक पुनरावर्ती सीटीई का एक उदाहरण है - ध्यान दें कि हालांकि परिणाम प्रश्न से मेल खाते हैं, लेकिन डेटा मुझे दिए गए विवरण से काफी मेल नहीं खाता है, क्योंकि मुझे लगता है कि आप वास्तव में पंक्तियों के समूहों पर ऐसा करना चाहते हैं, सभी नहीं मेज में पंक्तियां। तालिका में सभी पंक्तियों से मेल खाने के लिए इसे बदलने के लिए पाठक के लिए एक अभ्यास के रूप में छोड़ दिया जाता है।

;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

COALESCE प्रयोग करें:

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

बस कुछ स्पष्टीकरण (चूंकि यह उत्तर अपेक्षाकृत नियमित विचार प्राप्त करता है):

  • Coalesce वास्तव में केवल एक सहायक धोखा है कि दो चीजें पूरा करता है:

1) खाली स्ट्रिंग मान के साथ @Names को प्रारंभ करने की आवश्यकता नहीं है।

2) अंत में एक अतिरिक्त विभाजक को रोकने की जरूरत नहीं है।

  • उपर्युक्त समाधान गलत परिणाम देगा यदि पंक्ति में एक पूर्ण नाम मान है (यदि कोई नल है , तो NULL उस पंक्ति के बाद @Names बना @Names , और अगली पंक्ति फिर रिक्त स्ट्रिंग के रूप में शुरू हो जाएगी। आसानी से एक के साथ तय किया गया है दो समाधानों में से:
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

आप जिस व्यवहार को चाहते हैं उसके आधार पर (पहला विकल्प सिर्फ न्यूल आउट आउट करता है, दूसरा विकल्प उन्हें मार्कर संदेश के साथ सूची में रखता है ['एन / ए' को प्रतिस्थापित करें जो आपके लिए उपयुक्त है])।


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

आपको एक वैरिएबल बनाने की आवश्यकता है जो आपके अंतिम परिणाम को पकड़ लेगा और इसमें शामिल होगा।

सबसे आसान समाधान

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

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 के बाद से यह भी आसान है ।


ओरेकल में, यह wm_concat । मेरा मानना ​​है कि यह समारोह 10 जी रिलीज और उच्चतर में उपलब्ध है।


PostgreSQL 9.0 से शुरू करना यह काफी सरल है:

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

9.0 array_agg() से पहले के संस्करणों में array_agg() द्वारा दिखाए गए अनुसार उपयोग किया जा सकता है


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

एमएस एसक्यूएल सर्वर में XML data() कमांड के माध्यम से अभी तक एक विधि नहीं दिखाई गई है:

नामसूची नामक तालिका को FName नामक एक कॉलम के साथ मानें,

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

रिटर्न:

"Peter, Paul, Mary, "

केवल अतिरिक्त कॉमा के साथ निपटा जाना चाहिए।

संपादित करें: @ एनरिलिंग की टिप्पणी से अपनाए गए अनुसार, आप पिछली कॉमा को हटाने के लिए निम्न विधि का उपयोग कर सकते हैं। एक ही टेबल और कॉलम नाम मानते हैं:

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

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE' 
ORDER BY TABLE_NAME




sql sql-server csv string-concatenation group-concat