🙌🏽 👩‍❤️‍👩 🤶🏽 SQL सर्वर की दुनिया में स्ट्रिंग एकत्रीकरण ♐️ 👩🏿‍🤝‍👨🏽 👩🏼‍🎤

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

एमएसजी 8117, स्तर 16, राज्य 1, पंक्ति 1
ऑपरेटर डेटा प्रकार चार सम ऑपरेटर के लिए अमान्य है।

यद्यपि ऐसी समस्याओं को हल करने के लिए, MySQL के लिए GROUP_CONCAT फ़ंक्शन और Oracle में LISTAGG फ़ंक्शन जोड़ा गया था। बदले में, SQL सर्वर में ऐसी कोई अंतर्निहित कार्यक्षमता नहीं है।

हालाँकि, आपको इसे एक खामी के रूप में नहीं समझना चाहिए, क्योंकि टी-एसक्यूएल की क्षमताओं को अन्य निर्माणों के उपयोग के माध्यम से स्ट्रिंग लचीलेपन को अधिक लचीले और कुशलता से प्रदर्शन करने की अनुमति मिलती है, जिस पर बाद में चर्चा की जाएगी।

मान लें कि हमें निम्न तालिका से डेटा का उपयोग करके, एक पंक्ति में कई पंक्तियों को संयोजित करने की आवश्यकता है:

IF OBJECT_ID('dbo.Chars', 'U') IS NOT NULL DROP TABLE dbo.Chars GO CREATE TABLE dbo.Chars ([Char] CHAR(1) PRIMARY KEY) INSERT INTO dbo.Chars ([Char]) VALUES ('A'), ('B'), ('C'), ('F'), ('D')

इस समस्या का सबसे स्पष्ट समाधान कर्सर का उपयोग करना है:

 DECLARE @Chars VARCHAR(100) , @Char CHAR(1) DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR SELECT [Char] FROM dbo.Chars OPEN cur FETCH NEXT FROM cur INTO @Char WHILE @@FETCH_STATUS = 0 BEGIN SET @Chars = ISNULL(@Chars + ', ' + @Char, @Char) FETCH NEXT FROM cur INTO @Char END CLOSE cur DEALLOCATE cur SELECT @Chars

हालांकि, इसका उपयोग क्वेरी निष्पादन की दक्षता को कम करता है और, कम से कम, बहुत सुंदर नहीं दिखता है।

इससे छुटकारा पाने के लिए, आप वेरिएबल असाइन करके स्ट्रिंग्स को बदल सकते हैं:

 DECLARE @Chars VARCHAR(100) SELECT @Chars = ISNULL(@Chars + ', ' + [Char], [Char]) FROM dbo.Chars SELECT @Chars

एक तरफ, डिजाइन बहुत सरल निकला, दूसरी तरफ, एक बड़े नमूने पर इसका प्रदर्शन वांछित होने के लिए बहुत कुछ छोड़ देता है।

पंक्ति एकत्रीकरण के लिए, निम्नलिखित निर्माण का उपयोग करके XML के माध्यम से करना संभव है:

 SELECT Chars = STUFF(( SELECT ', ' + [Char] FROM dbo.Chars FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')

यदि आप योजना को देखते हैं, तो आप मूल्य पद्धति को कॉल करने की उच्च लागत को नोटिस करेंगे:

इस ऑपरेशन से छुटकारा पाने के लिए, आप XQuery गुणों का उपयोग करके क्वेरी को फिर से लिख सकते हैं:

 SELECT Chars = STUFF(CAST(( SELECT [text()] = ', ' + [Char] FROM dbo.Chars FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')

नतीजतन, हमें एक बहुत ही सरल और त्वरित निष्पादन योजना मिलती है:

सिद्धांत रूप में, एक एकल स्तंभ पर स्ट्रिंग संघनन बहुत कठिनाई का कारण नहीं बनता है।

एक और दिलचस्प स्थिति यह है कि जब आप एक साथ कई स्तंभों पर संघटन करना चाहते हैं। उदाहरण के लिए, हमारे पास निम्न तालिका है:

 IF OBJECT_ID('dbo.EntityValues', 'U') IS NOT NULL DROP TABLE dbo.EntityValues GO CREATE TABLE dbo.EntityValues ( EntityID INT , Value1 CHAR(1) , Value2 CHAR(1) ) CREATE NONCLUSTERED INDEX IX_WorkOut_EntityID ON dbo.EntityValues (EntityID) GO INSERT INTO dbo.EntityValues (EntityID, Value1, Value2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (2, 'C', 'Z'), (2, 'F', 'H'), (1, 'D', 'R')

जिसमें आप डेटा को निम्नानुसार समूहित करना चाहते हैं:

वैकल्पिक रूप से, आप XML कॉल को कॉपी कर सकते हैं, लेकिन फिर हमें डुप्लिकेट रीड मिलते हैं, जो क्वेरी की दक्षता को प्रभावित कर सकते हैं:

 SELECT ev.EntityID , Values1 = STUFF(CAST(( SELECT [text()] = ', ' + ev2.Value1 FROM dbo.EntityValues ev2 WHERE ev2.EntityID = ev.EntityID FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '') , Values2 = STUFF(CAST(( SELECT [text()] = ', ' + ev2.Value2 FROM dbo.EntityValues ev2 WHERE ev2.EntityID = ev.EntityID FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '') FROM ( SELECT DISTINCT EntityID FROM dbo.EntityValues ) ev

इसे आसानी से देखा जा सकता है यदि आप कार्यान्वयन योजना को देखते हैं:

बार-बार पढ़ने को कम करने के लिए, आप एक छोटी XML हैक का उपयोग कर सकते हैं:

 SELECT ev.EntityID , Values1 = STUFF(REPLACE( CAST([XML].query('for $a in /a return xs:string($a)') AS VARCHAR(100)), ' ,', ','), 1, 1, '') , Values2 = STUFF(REPLACE( CAST([XML].query('for $b in /b return xs:string($b)') AS VARCHAR(100)), ' ,', ','), 1, 1, '') FROM ( SELECT DISTINCT EntityID FROM dbo.EntityValues ) ev CROSS APPLY ( SELECT [XML] = CAST(( SELECT [a] = ', ' + ev2.Value1 , [b] = ', ' + ev2.Value2 FROM dbo.EntityValues ev2 WHERE ev2.EntityID = ev.EntityID FOR XML PATH('') ) AS XML) ) t

लेकिन क्वेरी विधि के बार-बार कॉल करने के कारण यह क्वेरी भी इष्टतम नहीं होगी।

आप कर्सर का उपयोग कर सकते हैं:

 IF OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL DROP TABLE #EntityValues GO SELECT DISTINCT EntityID , Values1 = CAST(NULL AS VARCHAR(100)) , Values2 = CAST(NULL AS VARCHAR(100)) INTO #EntityValues FROM dbo.EntityValues DECLARE @EntityID INT , @Value1 CHAR(1) , @Value2 CHAR(1) DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR SELECT EntityID , Value1 , Value2 FROM dbo.EntityValues OPEN cur FETCH NEXT FROM cur INTO @EntityID , @Value1 , @Value2 WHILE @@FETCH_STATUS = 0 BEGIN UPDATE #EntityValues SET Values1 = ISNULL(Values1 + ', ' + @Value1, @Value1) , Values2 = ISNULL(Values2 + ', ' + @Value2, @Value2) WHERE EntityID = @EntityID FETCH NEXT FROM cur INTO @EntityID , @Value1 , @Value2 END CLOSE cur DEALLOCATE cur SELECT * FROM #EntityValues

हालांकि, जैसा कि अभ्यास में दिखाया गया है, जब बड़े ईटीएल पैकेज के साथ काम करते हैं, तो सबसे अधिक उपयोगी समाधान यूपीआई परिणाम में चर को असाइन करने की क्षमता है:

 IF OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL DROP TABLE #EntityValues GO DECLARE @Values1 VARCHAR(100) , @Values2 VARCHAR(100) SELECT EntityID , Value1 , Value2 , RowNum = ROW_NUMBER() OVER (PARTITION BY EntityID ORDER BY 1/0) , Values1 = CAST(NULL AS VARCHAR(100)) , Values2 = CAST(NULL AS VARCHAR(100)) INTO #EntityValues FROM dbo.EntityValues UPDATE #EntityValues SET @Values1 = Values1 = CASE WHEN RowNum = 1 THEN Value1 ELSE @Values1 + ', ' + Value1 END , @Values2 = Values2 = CASE WHEN RowNum = 1 THEN Value2 ELSE @Values2 + ', ' + Value2 END SELECT EntityID , Values1 = MAX(Values1) , Values2 = MAX(Values2) FROM #EntityValues GROUP BY EntityID

SQL सर्वर में GROUP_CONCAT और LISTAGG फ़ंक्शन का अंतर्निहित एनालॉग नहीं है। हालाँकि, यह स्थिति पर निर्भर करता है, कुशलतापूर्वक स्ट्रिंग समालोचना कार्यों को करने के लिए हस्तक्षेप नहीं करता है। इस पोस्ट का उद्देश्य यह प्रदर्शित करना है।

SQL सर्वर की दुनिया में स्ट्रिंग एकत्रीकरण

More articles: