एक सबक्वायरी से एक ही सीमित क्षेत्र में "पंक्तियों" में शामिल होने के लिए SQL सर्वर फ़ंक्शन कैसे बनाएं?

उदाहरण के लिए, मान लीजिए कि मेरे पास दो टेबल हैं:

VehicleID Name
1         Chuck
2         Larry

LocationID VehicleID City
1          1         New York
2          1         Seattle
3          1         Vancouver
4          2         Los Angeles
5          2         Houston

मैं निम्नलिखित परिणामों को वापस करने के लिए एक प्रश्न लिखना चाहता हूं:

VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston

मुझे पता है कि यह सर्वर साइड कर्सर का उपयोग करके किया जा सकता है, यानी:

DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
  VehicleID int
  Name varchar(100)
  Locations varchar(4000)
)

DECLARE VehiclesCursor CURSOR FOR
SELECT
  [VehicleID]
, [Name]
FROM [Vehicles]

OPEN VehiclesCursor

FETCH NEXT FROM VehiclesCursor INTO
  @VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @Locations = ''

  DECLARE LocationsCursor CURSOR FOR
  SELECT
    [City]
  FROM [Locations]
  WHERE [VehicleID] = @VehicleID

  OPEN LocationsCursor

  FETCH NEXT FROM LocationsCursor INTO
    @LocationCity
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @Locations = @Locations + @LocationCity

    FETCH NEXT FROM LocationsCursor INTO
      @LocationCity
  END
  CLOSE LocationsCursor
  DEALLOCATE LocationsCursor

  INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations

END     
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor

SELECT * FROM @Results

हालांकि, जैसा कि आप देख सकते हैं, इसके लिए कोड का एक बड़ा सौदा आवश्यक है। मैं जो चाहता हूं वह एक सामान्य कार्य है जो मुझे ऐसा कुछ करने की अनुमति देगा:

SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles

क्या यह संभव है? या कुछ समान है?

0
ro fr bn
एक और पूर्ण प्रतिक्रिया के साथ एक समान उत्तर stackoverflow.com/a/17591536/1587302
जोड़ा लेखक Narkha, स्रोत

13 उत्तर

ध्यान दें कि मैट कोड के परिणामस्वरूप स्ट्रिंग के अंत में एक अतिरिक्त कॉमा होगा; लांस के पोस्ट में दिए गए लिंक में दिखाए गए अनुसार COALESCE (या उस मामले के लिए ISNULL) का उपयोग करना एक समान विधि का उपयोग करता है लेकिन आपको निकालने के लिए अतिरिक्त कॉमा के साथ नहीं छोड़ता है। पूर्णता के लिए, यहां sqlteam.com पर लांस के लिंक से प्रासंगिक कोड है:

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
    CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
0
जोड़ा
जोड़ा लेखक Lukáš Lánský, स्रोत
कोई अतिरिक्त कॉमा, जो अच्छा है, लेकिन मेरी राय में, Accepeted समाधान की तुलना में, पढ़ने और समझने के लिए भी बहुत आसान है। बहुत धन्यवाद!
जोड़ा लेखक Beska, स्रोत
विश्वसनीय आदेश के लिए आदेश जोड़ें?
जोड़ा लेखक Pete Alvin, स्रोत
@lukasLansky जब तक आप आदेश के बारे में परवाह नहीं करते हैं तब तक इसकी भरोसेमंद है
जोड़ा लेखक codeulike, स्रोत
यदि आप आदेश की परवाह नहीं करते हैं तो भी यह परिणाम से डेटा छोड़ सकता है।
जोड़ा लेखक Der_Meister, स्रोत

यदि आप SQL Server 2005 चला रहे हैं, तो आप एक कस्टम सीएलआर कुल फ़ंक्शन

सी # संस्करण:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSV:IBinarySerialize
{
    private StringBuilder Result;
    public void Init() {
        this.Result = new StringBuilder();
    }

    public void Accumulate(SqlString Value) {
        if (Value.IsNull) return;
        this.Result.Append(Value.Value).Append(",");
    }
    public void Merge(CSV Group) {
        this.Result.Append(Group.Result);
    }
    public SqlString Terminate() {
        return new SqlString(this.Result.ToString());
    }
    public void Read(System.IO.BinaryReader r) {
        this.Result = new StringBuilder(r.ReadString());
    }
    public void Write(System.IO.BinaryWriter w) {
        w.Write(this.Result.ToString());
    }
}
0
जोड़ा

मैं विश्वास नहीं करता कि इसे एक प्रश्न के भीतर करने का एक तरीका है, लेकिन आप इस तरह की चालें अस्थायी चर के साथ खेल सकते हैं:

declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations

select @s

कर्सर पर चलने से शायद यह कम कोड है, और शायद अधिक कुशल है।

0
जोड़ा
"मैं विश्वास नहीं करता कि इसे एक प्रश्न में करने का एक तरीका है" हां, वहां है। एसक्यूएल सर्वर 2005 में <कोड> एक्सएमएल और सीटीई दोनों थे।
जोड़ा लेखक T.J. Crowder, स्रोत
मैं काफी हद तक निश्चित हूं कि आप अंतिम पंक्ति से "शायद" ले सकते हैं।
जोड़ा लेखक Marc Gravell, स्रोत
यह विश्वसनीय नहीं है, निष्पादन योजना पर निर्भर करता है, पंक्तियां खो सकती हैं। KB
जोड़ा लेखक Der_Meister, स्रोत
इस तकनीक या सुविधा क्या कहा जाता है? जब एक SELECT @s = @s वैरिएबल असाइनमेंट किया जाता है जिसमें उसके मौजूदा मान शामिल होते हैं, और परिणाम सेट में प्रत्येक पंक्ति के लिए फिर से बनाया जाता है?
जोड़ा लेखक Baodad, स्रोत

संस्करण नोट: इस समाधान के लिए आपको संगतता स्तर सेट 90 या उससे अधिक के साथ SQL Server 2005 या अधिक का उपयोग करना होगा।

यह

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

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

नीचे दिया गया कोड एसक्यूएल सर्वर 2000/2005/2008 के लिए काम करेगा

CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
  DECLARE @csvCities VARCHAR(1000)
  SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
  FROM Vehicles 
  WHERE VehicleId = @VehicleId 
  return @csvCities
END

-- //Once the User defined function is created then run the below sql

SELECT VehicleID
     , dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID
0
जोड़ा
क्या आपने वाराकर (अधिकतम) की कोशिश की?
जोड़ा लेखक Binoj Antony, स्रोत
वह वचर (1000), यह किसी प्रकार की सीमा है, है ना? जब मैं कॉलम सूची पर एक समान संगतता क्वेरी चलाता हूं तो यह बनता है कि यह केवल 950 वर्णों को रोक देगा, चाहे आकार निर्दिष्ट हो।
जोड़ा लेखक John Leidegren, स्रोत

यदि आप SQL Server 2005 का उपयोग कर रहे हैं, तो आप FOR xml PATH कमांड का उपयोग कर सकते हैं।

SELECT [VehicleID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR xml PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]

कर्सर का उपयोग करने से यह बहुत आसान है, और लगता है कि यह काफी अच्छा काम करता है।

0
जोड़ा
क्या आपको ',' की बजाय लाइन ब्रेक डालने का विचार होगा? धन्यवाद, जेम्स
जोड़ा लेखक James Parish, स्रोत
मैं इस कोड को संशोधित करते समय थोड़ा उलझन में आया, इसलिए अब मेरा खुद का सवाल
जोड़ा लेखक James Parish, स्रोत
@ जेम्स आप इसे पूरा करने के लिए एक सीटीई का उपयोग कर सकते हैं: माईसीटीई (वाहन आईडी, नाम, स्थान) के साथ (चयन करें [वाहन आईडी], [नाम], (चयन करें CAST (शहर + ',' AS VARCHAR (MAX)) से [स्थान] एक्सएमएल पाथ ('') के लिए जहां (वाहन आईडी = वाहन। वेहिकलआईडी)) वाहन [वाहन] से स्थान के रूप में) वाहन चुनें, नाम, स्थान (स्थान, ',', चार्ज (10)) माइक से स्थान के रूप में
जोड़ा लेखक Mun, स्रोत
यह इस डेटा के साथ अच्छी तरह से काम करेगा, लेकिन यदि आपके डेटा में एक्सएमएल विशेष वर्ण हो सकते हैं (उदा। <,>, और) उन्हें प्रतिस्थापित किया जाएगा (<, आदि)
जोड़ा लेखक GilM, स्रोत
आप अल्पविराम से छुटकारा पाने के लिए STUFF फ़ंक्शन में सबक्वायरी को लपेट सकते हैं। बस ',' के साथ क्वेरी लीड करें और फिर सबक्वायरी को लपेटें: STUFF ( subquery , 1,2, '')
जोड़ा लेखक MickJuice, स्रोत
लाइन ब्रेक कैरेक्टर इस तरह डाले जा सकते हैं: 'कुछ टेक्स्ट' + CHAR (13) + CHAR (10) + 'अगली पंक्ति पर टेक्स्ट'।
जोड़ा लेखक thefellow3j, स्रोत

SQL सर्वर 2005+ में:

SELECT [VehicleID]
    , [Name]
    , [Locations] = Isnull( Stuff(
        ( SELECT N', ' + [City] FROM [Locations]
        WHERE VehicleID = a.VehicleID
        FOR xml PATH(''),TYPE ).value('text()[1]', 'nvarchar(max)')
    , 1, 2, N''), N'')
FROM [Vehicle] a
0
जोड़ा
एक्सएमएल को डीकोड करना चाहिए, अगर [शहर] की तरह चार और <>, आउटपुट बन जाएगा, & amp; & LT; & Gt; , यदि आप सुनिश्चित हैं कि [शहर] में उन विशेष वर्ण नहीं हैं, तो इसे हटाने के लिए सुरक्षित है। ? स्टीवन चोंग
जोड़ा लेखक Steven Chong, स्रोत
हाय बाओडाड, परिणाम वही हैं, लेकिन जैसा कि मैंने परीक्षण किया है, '' के बजाय 'टेक्स्ट() [1]' का उपयोग करते समय प्रदर्शन बेहतर होता है, कोई बड़ा अंतर नहीं
जोड़ा लेखक Steven Chong, स्रोत
+1। यह जवाब अंतर्निहित है। आपको यह उल्लेख करने के लिए संपादित करना चाहिए कि यह एकमात्र ऐसे उत्तरों में से एक है जो विशेष वर्णों से बच नहीं पाएगा जैसे <> आदि। यदि हम इसका उपयोग करते हैं तो परिणाम भी समान नहीं होंगे: .value ('।', 'nvarchar (अधिकतम)') ?
जोड़ा लेखक Baodad, स्रोत
मैंने यह हिस्सा निकाला: TYPE) .value ('text() [1]', 'nvarchar (max)') और यह अभी भी बहुत अच्छा काम करता है ... सुनिश्चित नहीं है कि क्या करना है ।
जोड़ा लेखक Adam Nofsinger, स्रोत

ओपी के रूप में मैं अन्य कॉलम (जिसे मैं सबसे अधिक अनुमान लगाता हूं) चुनना चाहता हूं, तो मैं xml के लिए (जैसा कि पहले पोस्ट किया गया है) देख सकता है। COALESCE (@var ... का उपयोग अन्य कॉलम को शामिल करने की अनुमति नहीं देता है।

अद्यतन करें: programmingsolutions.net "पीछे" कॉमा को हटाने का एक तरीका है। इसे एक अग्रणी कॉमा में बनाकर और MSSQL के STUFF फ़ंक्शन का उपयोग करके आप नीचे वर्ण के रूप में पहले वर्ण (अग्रणी अल्पविराम) को प्रतिस्थापित कर सकते हैं:

stuff(
    (select ',' + Column 
     from Table
         inner where inner.Id = outer.Id 
     for xml path('')
), 1,1,'') as Values
0
जोड़ा

In a single SQL query, without using the FOR xml clause.
A Common Table Expression is used to recursively concatenate the results.

-- rank locations by incrementing lexicographical order
WITH RankedLocations AS (
  SELECT
    VehicleID,
    City,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY City
    ) Rank
  FROM
    Locations
),
-- concatenate locations using a recursive query
-- (Common Table Expression)
Concatenations AS (
  -- for each vehicle, select the first location
  SELECT
    VehicleID,
    CONVERT(nvarchar(MAX), City) Cities,
    Rank
  FROM
    RankedLocations
  WHERE
    Rank = 1

  -- then incrementally concatenate with the next location
  -- this will return intermediate concatenations that will be 
  -- filtered out later on
  UNION ALL

  SELECT
    c.VehicleID,
    (c.Cities + ', ' + l.City) Cities,
    l.Rank
  FROM
    Concatenations c -- this is a recursion!
    INNER JOIN RankedLocations l ON
        l.VehicleID = c.VehicleID 
        AND l.Rank = c.Rank + 1
),
-- rank concatenation results by decrementing length 
-- (rank 1 will always be for the longest concatenation)
RankedConcatenations AS (
  SELECT
    VehicleID,
    Cities,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY Rank DESC
    ) Rank
  FROM 
    Concatenations
)
-- main query
SELECT
  v.VehicleID,
  v.Name,
  c.Cities
FROM
  Vehicles v
  INNER JOIN RankedConcatenations c ON 
    c.VehicleID = v.VehicleID 
    AND c.Rank = 1
0
जोड़ा
धन्यवाद! मुझे एक जटिल कोड अभिव्यक्ति में बूलियन वाक्यांशों की अलग पंक्तियों के रूप में व्यक्त एसक्यूएल कोड खंडों की एक श्रृंखला को परिवर्तित करना है, और आपकी विधि को आजमाने के लिए उत्साहित हूं।
जोड़ा लेखक Paul Chernoch, स्रोत
इसके लिए धन्यवाद। यह इस समस्या के कुछ समाधानों में से एक है जो चर, फ़ंक्शन, एक्सएमएल क्लॉज या सीएलआर कोड का उपयोग नहीं करता है। इसका मतलब है कि मैं हल करने के लिए अपने समाधान को अनुकूलित करने में सक्षम था टीएसक्यूएल शुरुआती चुनौती 4 - एकाधिक पंक्तियों से मूल्यों को जोड़ना
जोड़ा लेखक Iain Samuel McLean Elder, स्रोत
@PeonProgrammer नहीं, यह बड़े परिणाम सेट के लिए बहुत खराब काम करता है और आपको त्रुटि देने की संभावना है, "कथन पूरा होने से पहले अधिकतम रिकर्सन 100 समाप्त हो गया है।" (आप अंत में विकल्प (MAXRECURSION 0) निर्दिष्ट करके कार्य कर सकते हैं, लेकिन फिर आपकी क्वेरी हमेशा चलाने के लिए हमेशा ले सकती है।
जोड़ा लेखक Kirk Woll, स्रोत
क्या इसका अन्य समाधानों पर प्रदर्शन लाभ है?
जोड़ा लेखक PeonProgrammer, स्रोत

मुझे निम्न कार्य बनाकर समाधान मिला है:

CREATE FUNCTION [dbo].[JoinTexts]
(
  @delimiter VARCHAR(20) ,
  @whereClause VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @Texts VARCHAR(MAX)

    SELECT  @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto
    FROM    SomeTable AS T
    WHERE   T.SomeOtherColumn = @whereClause

    RETURN @Texts
END
GO

उपयोग:

SELECT dbo.JoinTexts(' , ', 'Y')
0
जोड़ा
यह माइक पॉवेल और बिनोज एंटनी के उत्तरों।
जोड़ा लेखक Andriy M, स्रोत
महान समाधान क्योंकि पठनीयता अन्य उत्तरों +1 से बेहतर थी
जोड़ा लेखक PeonProgrammer, स्रोत

इस सवाल का प्रयास करें

SELECT v.VehicleId, v.Name, ll.LocationList
FROM Vehicles v 
LEFT JOIN 
    (SELECT 
     DISTINCT
        VehicleId,
        REPLACE(
            REPLACE(
                REPLACE(
                    (
                        SELECT City as c 
                        FROM Locations x 
                        WHERE x.VehicleID = l.VehicleID FOR xml PATH('')
                    ),    
                    '',', '
                 ),
             '',''
            ),
        '', ''
        ) AS LocationList
    FROM Locations l
) ll ON ll.VehicleId = v.VehicleId
0
जोड़ा

मुन का जवाब मेरे लिए काम नहीं करता था इसलिए मैंने इसे काम करने के लिए उस जवाब में कुछ बदलाव किए। उम्मीद है कि यह किसी की मदद करता है। SQL सर्वर 2012 का उपयोग करना:

SELECT [VehicleID]
     , [Name]
     , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,City) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR xml PATH ('')), 1, 2, '') AS Locations
FROM [Vehicle]
0
जोड़ा