how to display columns values in single line in sql server

Question

How to display a single column values ofsql server in a single line or row. and seperating each values by * symbol.

i tried this:

SELECT GROUP_CONCAT(ordernum SEPARATOR ' , ') AS File_viewer FROM Functional WHERE OrderNum Order by asc;

my column is:

**ORDERNUM**
00
NULL
00
NULL
ZZ
2UN
ZZ
263923720
NULL
1234
^
0001
000000901
0
o
:

Answer 1

You can use STUFF.

Query

select stuff((
        select '*' + coalesce([ordernum], 'null')
        from [Functional]
        for xml path('')
    )
    , 1, 1, ''
) as [File_viewer];

Answer 2

Try this:

SELECT STUFF
(
    (
        SELECT ',' + ordernum AS File_viewer 
        FROM Functional 
        WHERE OrderNum Order by asc
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
    ,1
    ,1
    ,''
)