You need to add precision and scale when converting to DECIMAL. Otherwise it will use the default values (DECIMAL(18,0)) leading to integer values.

Try

SELECT

ISNULL(CONVERT(DECIMAL(20,2),@val),2) AS [column1]

FOR XML RAW('row'), elements, root('XMLData'),...

I agree with rwatson and, partially, with futura.

However, we managed to use ServiceBroker to replace a job that repeated every minute to check if new data are available... It took...

How about using CONVERT?

SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7)

Why don't you use a Tally (or Numbers) table using a left join, limited to max(SortOrder)? If you don't use a Tally table yet, please have a look at the...

Please provide ready to use sample data and your requested result based on those data.

Alternatively you could look into bcp for exporting data together with FOR XML (PATH/AUTO/RAW/EXPLICIT) to transform...

duplicate post.

A more detailed explanation can be found here.

I took your sample file and removed a few lines to demonstrate the concept and to reduce the reply post. Both, XML NAMESPACES and CROSS APPLY are described in more...

I think there is a misinterpretation between outsourcing and offshore outsourcing.

Example: IT staff can be outsourced to another company that is located right next door (or even in-house). We...

Would you mind telling me why the code I provided a few posts back won't work for you? Still unknown to me...

Thinking about it, you probably can avoid the dynamic SQL part if you'd replace your variable with the underlying subselect (again, untested):

SELECT

A.LoweredEmail,

D.RoleName

FROM dbo.aspnet_Membership AS A WITH (NOLOCK)

INNER JOIN...

AFAIK, the only way to get this to work is to use dynamic SQL (code snippet untested...).

DECLARE @sql NVARCHAR(4000)

SET @sql='

SELECT

A.LoweredEmail,

D.RoleName

FROM dbo.aspnet_Membership AS A WITH (NOLOCK)

INNER JOIN dbo.aspnet_Users AS...

Then you'd need to use the value of Column2 as a fixed value, since there is no other way to exclude Column0(0). Well,...

The following two threads might help you to understand the usage of FOR XML as well as STUFF:

http://www.sqlservercentral.com/Forums/FindPost679589.aspx

and

http://www.sqlservercentral.com/Forums/FindPost873866.aspx

The former has some sample code that should help you understand the...

SELECT * FROM YourTable

WHERE Column1 <> 'abc' OR (Column1 = 'abc' AND Column2='U')

or

SELECT * FROM YourTable

EXCEPT

SELECT * FROM YourTable

WHERE Column1 = 'abc' AND Column2<>'U'

If you want to get your results in one column rather than pivoted you could use the FOR XML "trick":

SELECT

ID, NAME,

STUFF((SELECT ', ' + Orders FROM YourTable t2 WHERE t2.ID...