help to write a query to retrieve all attributes for a

Question

help to write a query to retrieve all attributes for a

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

aitchkcandoo (1/18/2012)
Thank you venugopal.rr and Paul. I was thrown by the reference to ','. So, because the salesorderid is being output as XML, it must be cast as varchar datatype, if it is not already. If that is correct, thank you very much for the solution (which works). If that is not the correct explanation, let me know.
Bless you for your help.

The comma is a string type (varchar) which cannot be 'added' to SalesOrderID (an integer) directly, because the data types do not match. According to the rules of data type precedence, SQL Server tries to convert the string to an integer (',' -> error). You can find the data type precedence list here:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

xenophilia SSCommitted Points: 1891 More actions · Answer 2

That is really great to read relevant documentation. Integer has precendence over varchar. But, I had tried to convert the comma to an int using the CAST function and that didn't work. I didn't mention it because it did seem a little absurd. The rules of precedence should govern the data, but it applies to the syntax as well....as least as far as the error handling is concerned.

-

Paul White SSC Guru Points: 150468 More actions · Answer 3

aitchkcandoo (1/18/2012)
That is really great to read relevant documentation. Integer has precendence over varchar. But, I had tried to convert the comma to an int using the CAST function and that didn't work. I didn't mention it because it did seem a little absurd. The rules of precedence should govern the data, but it applies to the syntax as well....as least as far as the error handling is concerned.

SQL Server 2012 introduces the CONCAT function, which should make things easier in many situations.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

xenophilia SSCommitted Points: 1891 More actions · Answer 4

Paul,

you mean to eliminate need for CAST() use CONCAT to force the comma and salesorderid to be treated as a string?

I tried but got error

Msg 195, Level 15, State 10, Line 1

'CONCAT' is not a recognized built-in function name.

--where I use CONCAT (remember must create #temp first)

use AdventureWorks

go

select customerid, STUFF (CONCAT ('SELECT , ','salesorderid')

from #temp

where(customerid = results.customerid)for XML path ('')),1,2,'') as salesOrders

from #temp results

group by customerid;

-

Paul White SSC Guru Points: 150468 More actions · Answer 5

aitchkcandoo (1/18/2012)
I tried but got error 'CONCAT' is not a recognized built-in function name.

CONCAT is only available in SQL Server 2012, as I said.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

xenophilia SSCommitted Points: 1891 More actions · Answer 6

Sorry I missed that! Time to download a Denali trial.

-