How to create a Pipe delimited text file in stored procedure

Question

I need to create a stored procedure that would create a pipe delimited text file based on user requirements.

The table that I will use has only 6 columns with names different from user required fields.

Also, the number of columns that user wants is 23. Some of them we do not have data for. I just need to display them in the text file.

I'm not sure how to display the data lined up under appropriate column while skipping other optional columns.

I think I would need something like this:

OptionalColumn 1|DataColumn 1|OptionalColumn 2|DataColumn 2
                  12/12/2015                      Name 1
                  12/12/2015                      Name 2  

Or some other formatting for pipe delimited file.

How would I approach this? Never done something like this.

Answer 1

You could probably concatenate your fields with a simple select like

SELECT  '|' + YOUR_COLUMN_NAME

If you need more elaborate selections then perhaps this Stack Overflow approach may give you ideas Comma Separated results in SQL The example creates a comma separated list, but the principle is the same. It allows for concatenation of data from multiple rows for a same id.

Answer 2

Seems like you might be able to just use concatenation if your output is always consistent....

Select 'col1|col2|col3|coln....'
union
Select '' as extracol + '|' + realcol + '|' + '' as extracol2 + '|' + realcol2 +'...';

..which will produce a String with headers

if you need to discover the structure dynamically that's a different story, you'll have to use the system tables, but this might be a simple solution. Also if you have a ton of rows, this is not a great approach. If your app is going to stream entries to a file, it may be best to have your app create the file format, not the database stored proc