Concatenate a single Column value depending on Condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

Below are the 3 tables

>> **QuotationMaster**

C#

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

C#

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

C#

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

C#

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.

C#

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 19:05pm

Prathap Gangireddy

Add a Solution

2 solutions

Solution 1

Hi Prathap,

Check the following Script

SQL

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.

Posted 21-Feb-13 19:10pm

gvprabu

Updated 21-Feb-13 20:13pm

v2

Comments

Prathap Gangireddy 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 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 22-Feb-13 1:28am

Thanks prabhu for your time. Please add groupby to your script.Above script worked for me.

gvprabu 22-Feb-13 2:12am

Ok fine... :-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prathap Gangireddy · Accepted Answer · 2013-02-21T19:28:00

C#

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;