CODEWITHSUNDEEP
sql-serversql-server-express
Vijaya Pandey
Vijaya Pandey

Reputation: 4282

Get delimited data from table in SQL Server

I have a table named TBL_SUBACC and fields are: Account, Branch, Ftype, ArmCode,DrlsttrDate, CrlsttrDate, TelNo etc.

Currently I am getting | separated data using following query:

select account +'|'+ branch +'|' + armcode as delimited_data from
   ReportingFramework.dbo.TBL_SUBACC

This gives the result as:

    delimited_data
    111123|01|C0013
    111124|01|C0013
    1234121|05|C0324
    0120219|02|C0329
    0212108|03|C3232
    111121|01|C0013

This is exactly what I want. But, the above query requires all the fileds name to be given manually. I want to get delimited data of all fields without giving all column names.

How can I do select * to get delimited data from the table?

Upvotes: 0

Views: 591

Answers (2)

Devart
Devart

Reputation: 122002

Try this one -

Query:

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.test'

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = 'SELECT delimited_data = ' + STUFF((
    SELECT CHAR(13) + ' + ''|'' + ' + 
    CASE WHEN c.is_nullable = 1 
        THEN 'ISNULL(CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)),'''') '
        ELSE 'CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX))'
    END
    FROM sys.columns c WITH (NOWAIT)
    WHERE c.[object_id] = OBJECT_ID(@table_name)
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 10, CHAR(13)) + '
FROM ' + @table_name

PRINT @SQL

EXEC sys.sp_executesql @SQL

Output:

SELECT delimited_data = 
             CAST([WorkOutID] AS NVARCHAR(MAX))
     + '|' + CAST([DateOut] AS NVARCHAR(MAX))
     + '|' + CAST([EmployeeID] AS NVARCHAR(MAX))
     + '|' + CAST([DepartmentUID] AS NVARCHAR(MAX))
     + '|' + ISNULL(CAST([WorkPlaceUID] AS NVARCHAR(MAX)), '') 
     + '|' + ISNULL(CAST([CategoryID] AS NVARCHAR(MAX)), '') 
FROM dbo.test

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

declare @nsql nvarchar(max);
set @nsql = '
select ' + stuff((
    select '+''|''+isnull(cast(' + quotename(name) + ' as nvarchar(max)),'''') '
    from sys.columns
    where object_id=object_id('YOUR_TABLE_NAME')
    order by column_id
    for xml path(''), type).value('/','nvarchar(max)'), 1, 5, '') + ' as delimited_data
from YOUR_TABLE_NAME';
--print @nsql;
exec (@nsql);

Replace YOUR_TABLE_NAME (twice) with your target table. To do it properly, you need to cater for conversion to VARCHAR and NULLs. This works very poorly for datetimes, which come out in the terrible format for pipe-delimited consumption by another software. Better if you just stop at print @nsql and hand-tweak that before running.

Upvotes: 2

Related Questions