SQL Server 2008 concatenating strings?
I spent 2 days trying to fix this problem. Now I'm Going Crazy, I Need Urgent Help, The Problem Is I Have To Tables
Table: Sales, SalesId primary key
---------------------------------------------------
SalesId | SalesDate | Customer| Discount | Remarks
---------------------------------------------------
1 | 01/01/2012| John | 15 | NULL
2 | 01/01/2012| Peter | 25 | NULL
3 | 01/01/2012| Micheal | 35 | NULL
Table: SalesBody, SerialNo - primary key
and SalesId -foreign key
---------------------------------------------------
SerialNo | SalesId | Product | Quantity | Rate
---------------------------------------------------
10 | 1 | Pencil | 18 | 20
11 | 1 | pen | 200 | 60
12 | 1 | Rubber | 150 | 10
13 | 1 | Paper | 500 | 2
14 | 2 | Mouse | 15 | 190
15 | 2 | KeyBoard | 10 | 600
16 | 2 | Monitor | 5 | 2000
17 | 3 | Mobile | 2 | 15000
Now I want to make a query that can make the result like this
----------------------------------------------------------------------------
SalesId | SalesDate | Details | Amount
----------------------------------------------------------------------------
1 | 01/01/2012 | Sold: Pencil x 18 @ 20, Pen x 200 @ 60| xxxxxxx
| | Rubber x 150 @ 10, Paper x 500 @ 2 |
2 | 01/01/2012 | Sold: Mouse x 15 @ 190, Keyboard x 10 |
| | @ 600, Monitor x 5 @ 2000 | xxxxxxx
3 | 01/01/2012 | Sold: Mobile x 2 @ 15000 | xxxxxxx
I have tried different methods for example. Coalesce, Stuff, for XML PATH ('')
I would not be able to combine the detail line.
source to share
As far as I can see you just need to expand the SalesBody table and group with SalesID
Something along the lines of the below query should do the trick
select sb.SalesId, ( SELECT ', ' + sb2.Product + ' x ' + sb2.Quantity + ' @ ' + sb2.Rate
FROM SalesBody sb2
WHERE sb2.SalesId = sb.SalesId
FOR XML PATH('') ) AS Details
from SalesBody sb
group by sb.SalesId
Then just join that query to your Sales table to get other data and run the STUFF command in the Details section of the query above to remove the leading "," and add the "sold:" line and you should be fine. go over.
source to share