SQL Server 2008, concatenating Strings?

Question

I have wasted my 2 whole days to trying to resolve this issue, Now I am going out of my mind, I need an urgent Help, The Issue is, I Have To Tables

Table: Sales, SalesId is 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 is primary key and SalesId is 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 which can make the result like following

----------------------------------------------------------------------------
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 techniques eg. Coalesce, Stuff, For XML PATH('')

I could not Concatenate the Detail String.

AFAIK aggregate concatenation [is not trivial](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) in SQL server — default locale, Jan 28 '13 at 11:27
GROUP_CONCAT do same in MySQL, you can search alternative in SQL server.. http://explainextended.com/2010/06/21/group_concat-in-sql-server/ — kwelsan, Jan 28 '13 at 11:32
why are you trying to do that in SQL ? client side languages are way better for this scenario — WKordos, Jan 28 '13 at 12:16

score 6 · Accepted Answer · answered Jan 28 '13 at 12:17

6

As far as I can see you just need to pivot the SalesBody table and group by 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 with your Sales table to get the other data and do a STUFF command on Details in the query above to remove the leading "," and add your "sold: " string and you should be all good to go.

answered Jan 28 '13 at 12:17

LarsHJ

205
1
11

2

Nice. To replace the leading `,` with `Sold:`, something like `STUFF((SELECT ...), 1, 1, 'Sold:')` could do. – Andriy M Jan 29 '13 at 07:46
@Malik check out the link in my previous comment and accept this answer. Also you definitely need to visit [about page](http://stackoverflow.com/about) – default locale Feb 01 '13 at 07:18

SQL Server 2008, concatenating Strings?

1 Answers1