FW: Query
I want the return rows into columns, like The return rows in a select 1 2 3 4 5 Appear as 1 2 3 4 5 Is it possible? Thanx, S m sending to informix-list
Supposing you are on UNIX:
OUTPUT TO PIPE " tr '\\r' ' ' "
SELECT ....
should do it (it substitutes every CR with a space).
You need to create a stored procedure, as illustrated below.
--
Regards,
Doug Lawry
www.douglawry.webhop.org
-- Returning rows as columns in Informix SQL
-- Doug Lawry, comp.databases.informix, 09/09/2005
CREATE TEMP TABLE master (link INTEGER, data CHAR(3));
CREATE TEMP TABLE detail (link INTEGER, data CHAR(3));
INSERT INTO master VALUES (1, 'One');
INSERT INTO master VALUES (2, 'Two');
INSERT INTO detail VALUES (1, 'ABC');
INSERT INTO detail VALUES (1, 'DEF');
INSERT INTO detail VALUES (1, 'GHI');
INSERT INTO detail VALUES (1, 'JKL');
INSERT INTO detail VALUES (2, 'MNO');
INSERT INTO detail VALUES (2, 'PQR');
INSERT INTO detail VALUES (2, 'STU');
INSERT INTO detail VALUES (2, 'WXY');
CREATE FUNCTION get_details(v_link INTEGER)
RETURNING VARCHAR(255);
DEFINE v_data VARCHAR(255);
DEFINE v_list VARCHAR(255);
LET v_list = '';
FOREACH
SELECT data
INTO v_data
FROM detail
WHERE link = v_link
ORDER BY 1
LET v_list = v_list || v_data || ' ';
END FOREACH
RETURN v_list;
END FUNCTION;
-- Concatenated column
SELECT *,
get_details(link) AS details
FROM master;
{
link data details
1 One ABC DEF GHI JKL
2 Two MNO PQR STU WXY
}
-- Separate columns
SELECT *,
SUBSTR(get_details(link), 1, 3) AS d1,
SUBSTR(get_details(link), 5, 3) AS d2,
SUBSTR(get_details(link), 9, 3) AS d3,
SUBSTR(get_details(link), 13, 3) AS d4
FROM master;
{
link data d1 d2 d3 d4
1 One ABC DEF GHI JKL
2 Two MNO PQR STU WXY
}
DROP FUNCTION get_details;
DROP TABLE master;
DROP TABLE detail;
"shahid.mehmood" <shahid.mehmood@aku.edu> wrote in message
news:1126255085.6592cc18622845ba70c3cccc8293ee25@teranews...
>
> I want the return rows into columns, like
>
>
>
> The return rows in a select
>
> 1
>
> 2
>
> 3
>
> 4
>
> 5
>
>
>
> Appear as
>
> 1 2 3 4 5
>
>
>
> Is it possible?
>
>
>
> Thanx,
>
> S m
> sending to informix-list