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




sql-server csv (20)

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

Peter
Paul
Mary

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


एसक्यूएल सर्वर 2005 में

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

एसक्यूएल सर्वर 2016 में

आप फॉर जेएसओएन सिंटैक्स का उपयोग कर सकते हैं

अर्थात

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 सर्वर 2017 में, Azure SQL डेटाबेस में

आप नए STRING_AGG फ़ंक्शन का उपयोग कर सकते हैं


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

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

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

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

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


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;

इस जवाब को सर्वर में काम करने के लिए कुछ विशेषाधिकार की आवश्यकता होगी।

Assemblies लिए एक अच्छा विकल्प है। ऐसी कई साइटें हैं जो बताती हैं कि इसे कैसे बनाया जाए। मुझे लगता है कि यह बहुत अच्छी तरह से समझाया गया है यह one

यदि आप चाहते हैं, तो मैंने पहले से ही असेंबली बनाई है, और here डीएलएल डाउनलोड करना संभव here

एक बार इसे डाउनलोड करने के बाद, आपको अपने SQL सर्वर में निम्न स्क्रिप्ट चलाने की आवश्यकता होगी:

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

आशा करता हूँ की ये काम करेगा!!!


एक्सएमएल का उपयोग करने से मुझे पंक्तियों को अल्पविराम से अलग करने में मदद मिली। अतिरिक्त अल्पविराम के लिए हम SQL सर्वर के प्रतिस्थापन फ़ंक्शन का उपयोग कर सकते हैं। अल्पविराम जोड़ने के बजाय, एएस 'डेटा ()' का उपयोग रिक्त स्थान के साथ पंक्तियों को जोड़ देगा, जिसे बाद में नीचे लिखा गया वाक्यविन्यास के रूप में अल्पविरामों के साथ प्रतिस्थापित किया जा सकता है।

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

एमएस एसक्यूएल सर्वर में 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

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

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

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


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


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

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

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

मैं आमतौर पर 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

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

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

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

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

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

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


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

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

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

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

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





group-concat