MySQL Forums
Forum List  »  Stored Procedures

Cross-table in stored procedure
Posted by: Y BT
Date: October 14, 2011 08:31AM

Hello friends.
I need help to solve some task:
(On base MySQL Server 5.5.)
I get follow temp table (as a result of query):
+--------------------+
| id | date | value |
+--------------------+
| id1 | 1 | A |
| id2 | 1 | B |
| id1 | 2 | Aa |
| id2 | 2 | Ba |
| id3 | 2 | Ca |
| id2 | 3 | Bb |
| id3 | 3 | Cb |
| id4 | 3 | Db |
| id1 | 4 | Ad |
| id2 | 4 | Bd |
| id3 | 4 | Cd |
| id4 | 4 | Dd |
| id1 | 5 | Ae |
| id3 | 5 | Ce |
| id4 | 5 | De |
+--------------------+
I need transform it to some like this by creation temporary table:
+---------------------------------------+
| date | id1 | id2 | id3 | id4 |
+---------------------------------------+
| 1 | A | B | null | null |
| 2 | Aa | Ba | Ca | null |
| 3 | null | Bb | Cb | Db |
| 4 | Ad | Bd | Cd | Dd |
| 5 | Ae | null | Ce | De |
+---------------------------------------+
I used the cross-table algorithm according to MySQL Wizardry of Giuseppe Maxia
(http://dev.mysql.com/tech-resources/articles/wizard/index.html).
My code is:
CREATE TEMPORARY TABLE res_tbl AS (
SELECT DISTINCT date,
IF(id = id1, id1, null) AS 'id1',
IF(id = id2, id2, null) AS 'id2',
IF(id = id3, id3, null) AS 'id3',
IF(id = id4, id4, null) AS 'id4',
FROM tmp_tbl ORDER BY date);
Using this code I got following table:
+---------------------------------------+
| date | id1 | id2 | id3 | id4 |
+---------------------------------------+
| 1 | A | null | null | null |
| 1 | null | B | null | null |
| 2 | Aa | null | null | null |
| 2 | null | Ba | null | null |
| 2 | null | null | Ca | null |
| 3 | null | Bb | null | null |
| 3 | null | null | Cb | null |
| 3 | null | null | null | Db |
| 4 | Ad | null | null | null |
| 4 | null | Bd | null | null |
| 4 | null | null | Cd | null |
| 4 | null | null | null | Dd |
| 5 | Ae | null | Ce | De |
| 5 | null | null | Ce | null |
| 5 | null | null | null | De |
+---------------------------------------+
Where is an error? Or how can I solve this task by other way.
P.S. The code is in stored procedure.
Thanks,
YbT

Options: ReplyQuote


Subject
Views
Written By
Posted
Cross-table in stored procedure
2383
October 14, 2011 08:31AM
633
October 20, 2011 11:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.