Mysql group_concat convert MSSQL

Transact-SQL
1

Dear All,

I want to get the query statement for return 2. and the function is like the group_concat , but mssql is no this function to provide it. Ensure to display in each sales order and contain each item have entitle how many discount name information and sum qty , put all the discount names will save in one row . please look at the return 2 format.

Data

Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 2 Vip 10%

Sale trx number s01 |ANDY | Book | 002 1 item discount 15%

Sale trx number s01 |Andy | Book | 002 1 item discount 15%

Sale trx number s01 |Andy | cake | 003 3 father day discount 20%

Sale trx number s02 | tom | A510 | 004 1 item discount 15%

Sale trx number s02 | tom | A510 | 004 1 item discount 15%

Sale trx number s02 | tom | A511 | 005 2 vip 20%

Returns 1:

Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 2 Vip 10%

Sale trx number s01 |ANDY | Book | 002 2 item discount 15%

Sale trx number s01 |Andy | cake | 003 3 father day discount 20%

Sale trx number s02 | tom | A510 | 004 2 item discount 15%

Sale trx number s02 | tom | A511 | 005 2 vip 20%

Returns 2:
Sale number client item styleno Qty Discount name

Sale trx number s01 | ANDY | Apple | 001 7 Vip 10% + book item discount 15% + cake father day discount 20%
Sale trx number s02 | tom | A510 | 004 2 item discount 15%
Sale trx number s02 | tom | A511 | 005 2 vip 20%

Ps : yiuwanlungfelix@gmail.com

2

SQL Server just introduced the new STRING_AGG function, unfortunatly it isn't available in 2012...

For those of us who aren't on a version that supports the new function, we have FOR XML PATH('')...

There are plenty of articles and forum posts, explaining and demonstrating how to do group aggregations using FOR XML PATH, so I won;t go any deeper than that here...

Here is an example of the syntax:

	SELECT 
		sod1.SalesOrderID,
		STUFF((
			SELECT ', ' + CAST(sod2.ProductID AS VARCHAR(8))
			FROM Sales.SalesOrderDetail sod2
			WHERE sod1.SalesOrderID = sod2.SalesOrderID
			FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') AS csv
	FROM 
		Sales.SalesOrderDetail sod1
	GROUP BY 
		sod1.SalesOrderID;
3

Classic!

Just on a point of curiosity: any difference (in this specific example) between SELECT DISTINCT and GROUP BY?

4

Actually thinking about it DISTINCT may go off and make all the XMLs for each row and THEN de-dupe them to get the DISTINCT whereas, hopefully!, GROUP BY just figures out the unique SalesOrderID values and THEN creates the XML

5

I'm hell and gone away from being anything that even resembles an XML expert... I just kick it and beat on it until it works.
I keep a few scripts on hand, as reference, for when I need to use it.
Aside from that, I'm probably the very last person you'd want answering specific questions about XML...

6

I'm guessing that you're British... We Yanks think sod is is the grass on our lawns... I like the Brit meaning better...

7

Yes, sod the lawns ... :slight_smile: