Append results of a query in a single line

Question

I am unloading the results of a query to a file. I need the result records to be displayed in a single line. For example,

unload to file.unl select col from table where col = "test";

The resulting file.unl would be like

test|
test|
....
test|

But what I would like to get is,

test|test|test|....|test|

Any guidance or ideas are appreciated.

Thanks

@teran: Can you explain the **FOR XML PATH ..** is necessary in 4gl? — C0de_Hard, Jul 06 '12 at 10:26
sorry, I didn't note that you are using 4gl. answer above is for MS SQL; — teran, Jul 06 '12 at 11:17

score 2 · Accepted Answer · edited Jul 08 '12 at 21:08

You are probably aware you can declare DELIMITER "|" in the SQL or via the DBDELIMITER environment variable. As far as I know, there is no way to change the default record terminator from NEWLINE to anything else.

So I suspect you will need to either write a custom function that iterates through the results and appends them to a variable which you then write out to a file, or write a separate piece of script that you call via RUN that pivots the data from rows to columns, eg:

UNLOAD TO file.unl SELECT col FROM table WHERE ...;    
RUN "/usr/bin/perl -ni -e 'chomp; print' file.unl";

I've assumed you're running on Unix and have perl handy. If you don't, you might find the iterator suggestion more practical than mucking about with installing perl on Windows, particularly if you have limited control over machines this code gets run on.

Update

My 4GL is very rusty, but I was decribing something very basic, eg:

DEFINE command CHAR(10100)
DEFINE long_string CHAR(10000)
DECLARE curs1 CURSOR FOR
  SELECT col FROM table WHERE ...

FOREACH curs1 INTO col1
    LET long_string = long_string CLIPPED || col1 CLIPPED || '|'
END FOREACH
LET command = "echo '" || long_string CLIPPED || "' > file.unl"
RUN command

It's 15 years or more since I wrote any 4GL, so treat that as pseudo-code at most, please. There might be a better way to write directly to a file, I honestly can't remember.

I have been using Perl for a while. Is there a way to accomplish this without using scripts? — C0de_Hard, Jul 07 '12 at 12:59
Updated answer with simple iterator example. I still think the perl version is better; it's both less code and less susceptible to mangling by accidental shell interpretation. — RET, Jul 08 '12 at 10:45
I've fixed up the I4GL code a bit. The Perl mechanism, flattening all but the very last newline would the way to go; the I4GL code is delicate and vulnerable to overflow. — Jonathan Leffler, Jul 08 '12 at 21:07
An HPL or UNLOAD option for defining or suppressing the end of record marker would be desirable! — Joe R., Jul 09 '12 at 23:58

score 1 · Answer 2 · answered Jul 06 '12 at 10:21

1

CREATE Table #Table ( PKey INT Primary key, Field VARCHAR(10) )

INSERT INTO #Table select 1, 'ABS1' UNION ALL select 2, 'ABS2' UNION ALL select 3, 'ABS3'

DECLARE @results VARCHAR(MAX) = ''

select @results = COALESCE( @results + '|' + Field, @results ) from #Table

SELECT @results

answered Jul 06 '12 at 10:21

wnSarah-Marie Nothling

61
3

What language is this?? Why are you using @ before results? Please explain. – C0de_Hard Jul 06 '12 at 10:25
This is a sql query. The @ before the results means that I am creating a varchar variable to concatenate all my results into. I finally select that variable as my results. – wnSarah-Marie Nothling Jul 06 '12 at 10:29
@Sarah: Thank you for explaining.. Please understand that I am using Informix-4GL for querying. – C0de_Hard Jul 06 '12 at 10:35
I understand, it looked very similar to sql. – wnSarah-Marie Nothling Jul 06 '12 at 11:46
The `@` and `#` notation is MS SQL Server (or possibly Sybase) and very little to do with Informix-4GL. – Jonathan Leffler Jul 08 '12 at 20:23

Append results of a query in a single line

2 Answers2