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.