How to export data of sql query with | delimiter in txt format

Question

I have a query which give me output in the below format in sql

Column1  Column2 Column3
  X        Y       Z
  A        B       C

what I want is to export the output of that query into a txt file with column data as | delimited. for example:

Column1|Column2|Column3
 X|Y|Z
 A|B|C

Any help will be appreciated !!!

Answer 1

UPDATE: New approach for a generic solution

It is not trivial to query a result-set generically. In my mind there are two approaches:

• Use FOR XML RAW to transform the resultset into an XML and use XML's great abilities to deal with elements, nodes and attributes generically
• Use a CSV export

For the CSV approach you'll find tons of older questions. Start here

For the generic query approach have a look at this:

DECLARE @tbl TABLE(Column1 VARCHAR(100),Column2 VARCHAR(100),Column3 VARCHAR(100));
INSERT INTO @tbl VALUES ('X','Y','Z'),('A','B','C');

SELECT STUFF(r.value(N'text()[1]',N'nvarchar(max)'),1,1,'') AS Concatenated
FROM
(
    SELECT 
    (
        SELECT * FROM @tbl AS Tbl FOR XML RAW,TYPE --<-- This is your existing query
    )
) AS A(x)
OUTER APPLY
( SELECT x.query
    (N'
            for $r in /row
            return
            <row>
            {
                for $a in $r/@*
                return concat("|",$a)
            }
            </row>
    ')
) AS B(y)
OUTER APPLY y.nodes(N'/row') AS C(r);