Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 can have many items
 
  QuoteItemID  QuoteID    ItemCode    ItemID   
    ---------------------------------------------
       1          1         100          1 
       1          1         200          2       
       2          2         200          2  


 
>> **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 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.
Posted 21-Feb-13 20:05pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 fine, tel me why you added GROUP BY Clause in your Script..?
 

Regards,
GVPrabu.
  Permalink  
v2
Comments
Prathap Gangireddy at 22-Feb-13 1:22am
   
Without using GroupBy I am not getting the desired result.I have tried your script same unexpected resul. when I have added group by to your script I get the desired result.
Prathap Gangireddy at 22-Feb-13 1:27am
   
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;
Prathap Gangireddy at 22-Feb-13 1:28am
   
Thanks prabhu for your time. Please add groupby to your script.Above script worked for me.
gvprabu at 22-Feb-13 2:12am
   
Ok fine... :-)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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;
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 280
1 Sergey Alexandrovich Kryukov 279
2 CPallini 205
3 Maciej Los 197
4 Afzaal Ahmad Zeeshan 160
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100