lose less numbers when making a string from table SQL Server

I have a table with doubles like 0.681672875510799

so for example a dummy table:

a1      a2
-------------------------
1      0.681672875510799
NULL    1
NULL    NULL
NULL    NULL
NULL    NULL
NULL    NULL

When I do

DECLARE @CommaString nvarchar(max)
SET @CommaString = ''
SELECT @CommaString = 
STUFF(
(SELECT  ',' + CAST([col] AS VARCHAR) FROM (
 SELECT [a1] AS col FROM [ta] UNION ALL 
 SELECT [a2] AS col FROM [ta] 
) alldata FOR XML开发者_StackOverflow社区 PATH('')  )  ,   1  ,   1  ,  '' )
PRINT @CommaString;

This prints:

1,0.681673,1

so I am losing several decimals, which are also important, How do I modify the code to get 1,0.681672875510799,1 instead of 1,0.681673,1?

In your inner query:

SELECT  ',' + CAST([col] AS VARCHAR) 
FROM (
  SELECT [a1] AS col FROM [ta] 
  UNION ALL 
  SELECT CAST([a2] AS DECIMAL(18,15)) AS col FROM [ta]

Casting the FLOAT to a DECIMAL works for me (SQL Server 2008 R2). You may have to tweak the (18,15) to work with your data.

Just noticed one more thing that works (and probably more consistently):

SELECT  ',' + CONVERT(varchar(max), col, 128)
FROM (
  SELECT [a1] AS col FROM [ta] 
  UNION ALL 
  SELECT [a2] AS col FROM [ta]

Your problem is that you are casting to varchar without specifying the size, you need to do CAST([col] AS VARCHAR(max)

DECLARE @CommaString nvarchar(max)
SET @CommaString = ''
SELECT @CommaString = 
STUFF(
(SELECT  ',' + cast(CAST( [col] as decimal(22,19)) as varchar(30)) FROM (
 SELECT [a1] AS col FROM [#ta] UNION ALL 
 SELECT [a2] AS col FROM [#ta] 
) alldata FOR XML PATH('')  )  ,   1  ,   1  ,  '' )
PRINT @CommaString;

The problem is that you will get a lot of zeroes as decimals even for the integer values. You probably need to do some other transformation if you care about that.

EDIT: Including my table definition:

create table #ta 
(
   a1 int,
  a2 float
)

EDIT: changed my table definition again from decimal to float for column b and added double casting in my query.

It now produces: 1.0000000000000000000,1.0000000000000000000,0.6816728755107990500