CODEWITHSUNDEEP
sqlsql-servert-sql
trent fernandez
trent fernandez

Reputation: 339

How to order by clause with distinct

I have a table in sql server in which I have some values. For example as below

Transaction   ProductNo
------------- -------------
2001-01-01          1
2001-01-01          3
2001-01-01          4
2001-01-02          2
2001-01-02          3
2001-01-02          5

I have written a query as below

SELECT DISTINCT trans.[Transaction], 
STUFF(( SELECT ',' + trans1.ProductNo  AS [text()] FROM Transactions trans1 
WHERE trans.[Transaction]=trans1.[Transaction] ORDER BY trans1.[Transaction] FOR XML PATH('')), 1,1,'')[ProductNo] 
FROM Transactions trans

output as below:

Transaction   ProductNo
------------- -------------
2001-01-01        1,3,4
2001-01-02        2,3,5

but I am getting below exception

Conversion failed when converting the varchar value ',' to data type int.

Upvotes: 0

Views: 52

Answers (2)

clesiemo3
clesiemo3

Reputation: 1129

You'll need to convert your int column to a string in order to concatenate. Otherwise it is trying to turn your comma into an int which it can't.

STR() documentation to convert numbers to strings: link

See new query:

SELECT DISTINCT trans.[Transaction], 
STUFF(( SELECT ',' + str(trans1.ProductNo)  AS [text()] FROM Transactions trans1 
WHERE trans.[Transaction]=trans1.[Transaction] ORDER BY trans1.[Transaction] FOR     XML PATH('')), 1,1,'')[ProductNo] 
FROM Transactions trans

Upvotes: 1

Jonny
Jonny

Reputation: 2519

',' + trans1.ProductNo - here is the problem. SQL server thinks you are trying to add a comma to a number. Try changing trans1.ProductNoto CAST(trans1.ProductNo AS VARCHAR).

Upvotes: 2

Related Questions