By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

store procedure to transpose rows to columns

P: n/a
Chris Eaton created a SP to to transpose rows to columns :
Here is an example of a rowtocol stored proc that takes a SQL statement as
the first paramter, a delimiter as the second parameter and the ouput (in the
3rd parameter) is the rows coverted to a column with the delimiter specified
used to separate the row values.

CREATE PROCEDURE rowtocol
(IN p_slct VARCHAR(4000), IN p_dlmtr VARCHAR(4000), OUT lc_str VARCHAR(4000))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE lc_colval VARCHAR(4000);
DECLARE c_refcur INT;
DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE C1 CURSOR FOR S1;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

PREPARE S1 FROM p_slct;

SET lc_str = '';
OPEN C1;
fetch_loop:
LOOP
FETCH C1 INTO lc_colval;
IF at_end = 1 THEN LEAVE fetch_loop;
END IF;
SET lc_str = lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE C1;
END

i have the following table:
INPUT_TABLE:

AGENT AMOUNT PRODUCT ORDER_ID
----- -------------------- --------------------------------------
AA 20 P1 1
BB 20 P1 1
XX 20 P1 1
BB 7 P2 2
CC 7 P2 2
CC 12 P2 3

How to call this SP to convert input_table rows into columns?
Thank's in advance. Leny G.

--
Message posted via http://www.dbmonster.com

Jun 27 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Apr 21, 10:31 am, "lenygold via DBMonster.com" <u41482@uwewrote:
Chris Eaton created a SP to to transpose rows to columns :
Here is an example of a rowtocol stored proc that takes a SQL statement as
the first paramter, a delimiter as the second parameter and the ouput (in the
3rd parameter) is the rows coverted to a column with the delimiter specified
used to separate the row values.

CREATE PROCEDURE rowtocol
(IN p_slct VARCHAR(4000), IN p_dlmtr VARCHAR(4000), OUT lc_str VARCHAR(4000))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE lc_colval VARCHAR(4000);
DECLARE c_refcur INT;
DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE C1 CURSOR FOR S1;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

PREPARE S1 FROM p_slct;

SET lc_str = '';
OPEN C1;
fetch_loop:
LOOP
FETCH C1 INTO lc_colval;
IF at_end = 1 THEN LEAVE fetch_loop;
END IF;
SET lc_str = lc_str || p_dlmtr || lc_colval;
END LOOP;
CLOSE C1;
END

i have the following table:
INPUT_TABLE:

AGENT AMOUNT PRODUCT ORDER_ID
----- -------------------- --------------------------------------
AA 20 P1 1
BB 20 P1 1
XX 20 P1 1
BB 7 P2 2
CC 7 P2 2
CC 12 P2 3

How to call this SP to convert input_table rows into columns?
Thank's in advance. Leny G.

--
Message posted viahttp://www.dbmonster.com
Not to detract from Chris's work, but you may want to investigate
instead using a recursive common table expression.

--Jeff
Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.