根据条件连接单个Column值 [英] Concatenate a single Column value depending on Condition

问题描述

以下是3张桌子



>> ** QuotationMaster **

Hi,

Below are the 3 tables

>> **QuotationMaster**

QuoteID QuoteNo  CustomerName
 -----------------------------
 1       Q1      Name1
 2       Q2     Name2
 3       Q3      Name3
 4       Q4      Name4
 5       Q5      Name5

>> ; ** QuoteItemDetails ** One Quote可以有很多商品

>> **QuoteItemDetails** One Quote can have many items

QuoteItemID  QuoteID    ItemCode    ItemID
  ---------------------------------------------
     1          1         100          1
     1          1         200          2
     2          2         200          2

< br $> b $ b

>> ** QuoteBatchDetails **一个QuoteItem可以有很多批次QuoteID和ItemID是常见的列。 BatchNo是varchar

>> **QuoteBatchDetails** One QuoteItem can have many Batches QuoteID and ItemID are the common columns. BatchNo is varchar

QuotebatchID  QuoteID   BatchNo    ItemID
  ---------------------------------------------
     1          1          A          1
     2          1          B          1
     3          1          C          2
     4          2          E          2
     5          2          F          2

我希望结果为

I want the result as

QuoteID    QuoteNo     CustName  ItemCode  BatchNo
-------------------------------------------------
   1          Q1         Name1     100       A,B
   1          Q1         Name1     200        C
   2          Q2         Name2     200       E,F

I想要创建一个程序，它将QuoteID作为INT类型的参数并得到如上所示的结果。



我面临的唯一问题是连接BatchNo，这取决于ItemID和进一步的QuoteID。



使用下面的查询我能够连接特定ID的BatchNo，但我不知道如何将它添加到主程序，当我这样做时，弹出的错误就像子查询返回多个值。我明白因为每个引用都可以有mo超过1项。

I want to create a procedure which takes QuoteID as parameter of INT type and get the result as above.

The only problem I am facing is to concatenate the BatchNo which depends on ItemID and further on QuoteID.

Using the below query I am able to concatenate the BatchNo for a particular ID but I am not sure how to add this to the main procedure, when I do that errors pops up like subquery returns more than one value.I understand because for every quote there can be more than 1 item.

select 
        ID.QuoteID,ID.ItemID,
        stuff((select ', ' + BatchNo 
               from SD_QuoteBatchDetails BD where ID.ItemID=BD.ItemID and ID.QuoteID=BD.QuoteID       
               for xml path('')),
              1,2,'') [Values]
    from SD_QuoteItemDetails QID,SD_QuoteBatchDetails ID where ID.QuoteID=QID.QuoteID 
    group by ID.ItemID,ID.QuoteID

任何人都可以提供相同的查询。

Can anyone provide a query for the same.

推荐答案

您好Prathap，



查看以下脚本

Hi Prathap,

Check the following Script

SELECT ID.QuoteID,ID.ItemID,
       STUFF((SELECT ',' + BatchNo  AS [text()]
               FROM SD_QuoteBatchDetails BD where ID.ItemID=BD.ItemID and ID.QuoteID=BD.QuoteID       
               FOR XML PATH('')),1,1,'') [BatchNo]
FROM SD_QuoteItemDetails QID,
INNER JOIN SD_QuoteBatchDetails ID ON ID.QuoteID=QID.QuoteID
GROUP BY ID.ItemID,ID.QuoteID

okei罚款，请告诉我为什么你在你的剧本中添加了GROUP BY Clause ..？





问候，

GVPrabu。

okei fine, tel me why you added GROUP BY Clause in your Script..?


Regards,
GVPrabu.

SELECT  b.QuoteItemID, 
        a.QuoteNo,
        a.CustomerName,
        b.ItemCode,
        c.BatchList
FROM    QuotationMaster a
        INNER JOIN QuoteItemDetails b
            ON a.QuoteID = b.QuoteID
        INNER JOIN
        (
          SELECT
               QuoteID, 
               ItemID,
               STUFF(
                   (SELECT ', ' + BatchNo
                    FROM   QuoteBatchDetails
                    WHERE  QuoteID = a.QuoteID AND
                           ItemID = a.ItemID
                    FOR XML PATH (''))
                    , 1, 1, '')  AS BatchList
          FROM  QuoteBatchDetails AS a
          GROUP BY QuoteID, ItemID
        ) c ON  b.QuoteID = c.QuoteID  AND
                b.ItemID = c.ItemID;

这篇关于根据条件连接单个Column值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！