change scientific notation in SQL server

Question

I have a table with 2 columns [supplier_code], [EAN] both as bigint.

For instance:

1. 1000 / 234562
2. 1001 / 332221
3. 1000 / 222221

The problem is when I use group concatenation it converts to scientific notation

 1. 1000 / 2.35E+05 and 2.22E+05
 2. 1001 / 2.22E+05

The result I want is

 1. 1000 / 234562 and 222221
 2. 1001 / 332221

This sounds more like a display question than an actual result question. What are you displaying this in? — JohnHC, Jan 12 '18 at 15:10
SELECT [Supplier Code], [EAN] = master.dbo.GROUP_CONCAT_DS(DISTINCT [EAN], ' and ', 1) INTO #ean FROM ean — D.Alex, Jan 12 '18 at 15:12
`GROUP_CONCAT_DS` is not a standard function. Is it [this `GROUP_CONCAT_DS`](https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/blob/master/GroupConcat/GROUP_CONCAT_DS.cs)? Try explicitly `CONVERT`ing your source data to an `NVARCHAR` of the appropriate format first, before feeding it to the custom aggregate. — Jeroen Mostert, Jan 12 '18 at 15:14
It looks like the output you're looking for is a string like '1000/234562, 222221' etc. If that's the case, then [something like this](https://stackoverflow.com/a/194887/2655263) doesn't require a third-party function; it becomes just a string concatenation thing. — Kevin Fichter, Jan 12 '18 at 15:23
Possible duplicate of [convert float into varchar in SQL server without scientific notation](https://stackoverflow.com/questions/6521354/convert-float-into-varchar-in-sql-server-without-scientific-notation) — Bacon Bits, Jan 12 '18 at 16:01
Use [`STR()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql) instead of casting to a varchar. You may need to use `LTRIM(STR())`. — Bacon Bits, Jan 12 '18 at 16:03

LukStorms · Answer 1 · 2018-01-12T16:08:08.443

You could have someone debug the code for that custom GROUP_CONCAT_DS function.

Or use standard T-SQL to do a group concat.

For example:

declare @TestEAN table (supplier_code bigint, EAN bigint);

insert into @TestEAN (supplier_code, EAN) values
(1000, 1234567890123456701),
(1000, 1234567890123456702),
(1001, 1234567890123456789);

IF OBJECT_ID('tempdb..#tmpEAN') IS NOT NULL DROP TABLE #tmpEAN;

SELECT supplier_code as [Supplier Code],
[EAN] = STUFF((
          SELECT ' and ' + cast(t2.EAN as varchar(30))
          FROM @TestEAN t2
          WHERE t.supplier_code = t2.supplier_code
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '')
INTO #tmpEAN
FROM (
    select supplier_code
    from @TestEAN 
    group by supplier_code
) t;

select * from #tmpEAN;

Note that SQL Server 2017 includes a STRING_AGG function.

change scientific notation in SQL server

1 Answers1