| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
12-19-11, 17:37
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
|
Splitting a row in Mulitple columns
|
|
Hi All,
I am new to db2 and I know I have done this in mysql but not sure if it can be done in db2 too with a sql query. I need to put the data of one column ( a row ) into multiple columns. How can I do that using a sql query . Please let me know
Regards
|
|
12-19-11, 23:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Although I thought that there were already some threads in this forum discussing to decompose a string into a column of multiple rows,
I couldn't find that.
If you could that, it is easy to transform rows into columns.
See "PIVOT" in "SQL on Fire! Part 1"
http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf
Anyway, if you published your sql code in mysql and DB2 version/release and platform OS on which you were working,
it might be easy to migrate the code to DB2.
|
Last edited by tonkuma; 12-19-11 at 23:13.
Reason: Correct some English.
|
12-20-11, 20:45
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
|
|
Hi Tonkuma,
Basically what I am trying to do is within a row trying to extract data between two funny characters and put it in a separate column using a sql. The funny characters are '@' and '/' . I am trying to search the web but didnot find an example for this. Could you please help,
Thanks
|
|
12-20-11, 21:14
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
I don't see anything particularly funny about the at symbol and the forward slash, but you can use either LOCATE() or INSTR() function to determine their positions, then SUBSTR() to extract the substring you need.
|
|
12-20-11, 21:56
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
ok I got it working until where it can find every occurrence of the first character and put it in a separate column but now I am stuck where I need to find the occurrence of the second character and get rid of the rest of the data since the rest of the data goes into its own column, using the locate and substr
thanks
|
|
12-21-11, 00:26
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please supply sample data and expected results.
The sample data should have enough rows to include various exceptional conditions.
|
|
12-22-11, 11:23
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
ok now there is a different issue with this, so I had to change the
query, now I am trying to extract data between the '\n' and a ':'
so here is my query for that
select id, text
, 1 rk
, substr( text,locate_in_string(text,':',1,1)+1
,locate_in_string(text,chr(10),1,1)-(locate_in_string(text,':',1,1)+1)) first
--, substr(text,locate_in_string(text,chr(10),1,1) + 1,
--length(text) - locate_in_string(text,chr(10),1,1) + 1 ) rem
from db2inst1.cri_customer
--where id in ( 44,44,46);
ERROR : A numeric argument of a built-in string function is out of range..
SQLCODE=-138, SQLSTATE=22011, DRIVER=3.58.81
when I run this for above ids it runs fine but when I run for all the ids
it runs out of range, my placement for chr(10) is not quite right. Could you help me
thanks
|
|
12-22-11, 14:04
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
Here is some sample data
Approval list: Members
Tenant type: Tenant
Reason Code: Not accepted due variety of Reasons
Refer: Document No1
I am extracting data between : and \n now
thanks
|
|
12-23-11, 00:07
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
The following example assumed that characters ':' and new line(CHR(10) or x'0A') were paired with same order.
Example 1: added some test data
Code:
------------------------------ Commands Entered ------------------------------
WITH cri_customer(id , text) AS (
VALUES ( 1 ,
'Approval list: Members' || x'0A' ||
'Tenant type: Tenant' || x'0A' ||
'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
'Refer: Document No1' )
, ( 101 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 1' || x'0A' ||
'Title: A Kiss for Little Bear' || x'0A' ||
'Author: Else Homelund Minarik' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 102 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 2' || x'0A' ||
'Title: The Moon Jumpers' || x'0A' ||
'Author: Janice May Udry' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 103 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 3' || x'0A' ||
'Title: The Goodnight Gecko' || x'0A' ||
'Author: Gill McBarnet' || x'0A' ||
'Pictures: Gill McBarnet' || x'0A' ||
'Note: Published by Hawaiian publisher' )
, ( 999 , 'garbage?' || x'0A' || 'xxxx' )
)
/*
SELECT * FROM cri_customer
*/
SELECT id
, SMALLINT( (rnum + 1) / 2 ) AS line
, SUBSTR( MAX(text)
, MIN(k) + 1
, NULLIF( MAX(k) , MIN(k) ) - MIN(k) - 1
) AS extracted
FROM cri_customer
LEFT OUTER JOIN
LATERAL
(SELECT k
, ROW_NUMBER() OVER(ORDER BY k) AS rnum
FROM LATERAL
(SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8
, 9,10,11,12,13,14,15,16 ) k(k1)
INNER JOIN
(VALUES 0, 16, 32, 48, 64, 80, 96,112
,128,144,160,176,192,208,224,240 ) k(k2)
ON k1 + k2 <= LENGTH(text) + 1
INNER JOIN
(VALUES 0, 256, 512, 768,1024,1280,1536,1792
,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
ON k1 + k2 + k3 <= LENGTH(text) + 1
) k(k)
WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
)
ON MOD(rnum , 2) = 0
OR SUBSTR(text , k , 1) = ':'
GROUP BY
id
, (rnum + 1) / 2
ORDER BY
id
, line
;
------------------------------------------------------------------------------
ID LINE EXTRACTED
----------- ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 Members
1 2 Tenant
1 3 Not accepted due variety of Reasons
1 4 Document No1
101 1 Personal library
101 2 Picture Books
101 3 1
101 4 A Kiss for Little Bear
101 5 Else Homelund Minarik
101 6 Maurice Sendak
102 1 Personal library
102 2 Picture Books
102 3 2
102 4 The Moon Jumpers
102 5 Janice May Udry
102 6 Maurice Sendak
103 1 Personal library
103 2 Picture Books
103 3 3
103 4 The Goodnight Gecko
103 5 Gill McBarnet
103 6 Gill McBarnet
103 7 Published by Hawaiian publisher
999 1 -
24 record(s) selected.
|
Last edited by tonkuma; 12-23-11 at 09:53.
Reason: Replace last test data("999,garbage?") and ON condition.
|
12-23-11, 00:31
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Calculate once the expression "(rnum + 1) / 2" in select-list and group by clause.
Example 1a:
Code:
SELECT id
, line
, SUBSTR( MAX(text)
, MIN(k) + 1
, NULLIF( MAX(k) , MIN(k) ) - MIN(k) - 1
) AS extracted
FROM cri_customer
LEFT OUTER JOIN
LATERAL
(SELECT k
, ROW_NUMBER() OVER(ORDER BY k) AS rnum
FROM LATERAL
(SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8
, 9,10,11,12,13,14,15,16 ) k(k1)
INNER JOIN
(VALUES 0, 16, 32, 48, 64, 80, 96,112
,128,144,160,176,192,208,224,240 ) k(k2)
ON k1 + k2 <= LENGTH(text) + 1
INNER JOIN
(VALUES 0, 256, 512, 768,1024,1280,1536,1792
,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
ON k1 + k2 + k3 <= LENGTH(text) + 1
) k(k)
WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
)
ON MOD(rnum , 2) = 0
OR SUBSTR(text , k , 1) = ':'
CROSS JOIN
LATERAL
(VALUES SMALLINT( (rnum + 1) / 2 ) ) f(line)
GROUP BY
id
, line
ORDER BY
id
, line
;
|
|
12-23-11, 11:01
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 26
|
|
Hi Tonkuma,
This method works like a charm, but
1. The occurence of \n is not fixed length, it varies for the rows
3. there are rows in the column for some ids that are in
one straight line and it doesnot have occurence of any of the above
characters in it. I am getting Null value for those rows.
ID LINE EXTRACTED
15 NULL NULL
47 NULL NULL
2. the query extract the data in seperate rows I need to put them in separate columns
thanks
|
|
12-23-11, 11:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
1. The occurence of \n is not fixed length, it varies for the rows
|
No problem.
I showed some data with different number of lines and different length of each lines in test data of Example 1.
Quote:
|
2. the query extract the data in seperate rows I need to put them in separate columns
|
I already wrote "it is easy to transform rows into columns".
See ...
Quote:
Originally Posted by tonkuma
|
|
|
12-23-11, 11:58
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
3. there are rows in the column for some ids that are in
one straight line and it doesnot have occurence of any of the above
characters in it. I am getting Null value for those rows.
ID LINE EXTRACTED
15 NULL NULL
47 NULL NULL
|
I made consciously the query that way.
See id = 999 in Example 1.
Now, I understand that I have misunderstood your requirements.
It would be easy to modify the query.
|
|
12-23-11, 12:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please look and check the test data and the results of id = (905, 907, 996, 997).
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
WITH cri_customer(id , text) AS (
VALUES ( 1 ,
'Approval list: Members' || x'0A' ||
'Tenant type: Tenant' || x'0A' ||
'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
'Refer: Document No1' )
, ( 101 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 1' || x'0A' ||
'Title: A Kiss for Little Bear' || x'0A' ||
'Author: Else Homelund Minarik' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 102 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 2' || x'0A' ||
'Title: The Moon Jumpers' || x'0A' ||
'Author: Janice May Udry' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 103 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 3' || x'0A' ||
'Title: The Goodnight Gecko' || x'0A' ||
'Author: Gill McBarnet' || x'0A' ||
'Pictures: Gill McBarnet' || x'0A' ||
'Note: Published by Hawaiian publisher' )
, ( 905 ,
'one straight line shoud display as it is.' )
, ( 907 ,
' 1: Frst line data' || x'0A' ||
' 2: Second line data' || x'0A' ||
' ... rest of line without colon' )
, ( 996 ,
'first line without colon, followed by some lines without colon ... garbage?' || x'0A' ||
'second line without colon' || x'0A' ||
'third line without colon' ) /* multiple lines without colon */
, ( 997 ,
'first line without colon, followed by some lines(some of them having colon) ... garbage?' || x'0A' ||
'second line: having colon' ) /* multiple lines with some front lines having no colon */
)
/*
SELECT * FROM cri_customer
*/
SELECT id
, COALESCE(line , 1) AS line
, SUBSTR( text
, COALESCE( MIN(k) , 0 ) + 1
, NULLIF( COALESCE( MAX(k) , LENGTH(text) + 1 ) , MIN(k) ) - COALESCE( MIN(k) , 0 ) - 1
) AS extracted
FROM cri_customer
LEFT OUTER JOIN
LATERAL
(SELECT k
, ROW_NUMBER() OVER(ORDER BY k) AS rnum
FROM LATERAL
(SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8
, 9,10,11,12,13,14,15,16 ) k(k1)
INNER JOIN
(VALUES 0, 16, 32, 48, 64, 80, 96,112
,128,144,160,176,192,208,224,240 ) k(k2)
ON k1 + k2 <= LENGTH(text) + 1
INNER JOIN
(VALUES 0, 256, 512, 768,1024,1280,1536,1792
,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
ON k1 + k2 + k3 <= LENGTH(text) + 1
) k(k)
WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
)
ON MOD(rnum , 2) = 0
OR SUBSTR(text , k , 1) = ':'
CROSS JOIN
LATERAL
(VALUES SMALLINT( (rnum + 1) / 2 ) ) f(line)
GROUP BY
id
, line
, text
ORDER BY
id
, line
;
------------------------------------------------------------------------------
ID LINE EXTRACTED
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 Members
1 2 Tenant
1 3 Not accepted due variety of Reasons
1 4 Document No1
101 1 Personal library
101 2 Picture Books
101 3 1
101 4 A Kiss for Little Bear
101 5 Else Homelund Minarik
101 6 Maurice Sendak
102 1 Personal library
102 2 Picture Books
102 3 2
102 4 The Moon Jumpers
102 5 Janice May Udry
102 6 Maurice Sendak
103 1 Personal library
103 2 Picture Books
103 3 3
103 4 The Goodnight Gecko
103 5 Gill McBarnet
103 6 Gill McBarnet
103 7 Published by Hawaiian publisher
905 1 one straight line shoud display as it is.
907 1 Frst line data
907 2 Second line data
996 1 -
997 1 -
28 record(s) selected.
|
|
12-24-11, 04:28
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Example 3: Extract data into rows by using Example 1 or 2, then convert rows to columns.
The code from previous examples was modified a little to meet the requirements and to eliminate repeated calculation of same expression.
Note: CAST(extracted AS VARCHAR(41) ) was used for ease of see of the result.
41 is the maximum length of extracted string in test data.
You may want to make it larger or want to eliminate casting itself.
Code:
------------------------------ Commands Entered ------------------------------
WITH cri_customer(id , text) AS (
VALUES ( 1 ,
'Approval list: Members' || x'0A' ||
'Tenant type: Tenant' || x'0A' ||
'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
'Refer: Document No1' )
, ( 101 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 1' || x'0A' ||
'Title: A Kiss for Little Bear' || x'0A' ||
'Author: Else Homelund Minarik' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 102 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 2' || x'0A' ||
'Title: The Moon Jumpers' || x'0A' ||
'Author: Janice May Udry' || x'0A' ||
'Pictures: Maurice Sendak' )
, ( 103 ,
'Book list: Personal library' || x'0A' ||
'Book type: Picture Books' || x'0A' ||
'Sequence number: 3' || x'0A' ||
'Title: The Goodnight Gecko' || x'0A' ||
'Author: Gill McBarnet' || x'0A' ||
'Pictures: Gill McBarnet' || x'0A' ||
'Note: Published by Hawaiian publisher' )
, ( 905 ,
'one straight line shoud display as it is.' )
, ( 907 ,
' 1: Frst line data' || x'0A' ||
' 2: Second line data' || x'0A' ||
' ... rest of line without colon' )
, ( 996 ,
'first line without colon, followed by some lines without colon ... garbage?' || x'0A' ||
'second line without colon' || x'0A' ||
'third line without colon' ) /* multiple lines without colon */
, ( 997 ,
'first line without colon, followed by some lines(some of them having colon) ... garbage?' || x'0A' ||
'second line: having colon' ) /* multiple lines with some front lines having no colon */
)
/*
SELECT * FROM cri_customer
*/
, elements_in_rows AS (
SELECT id
, line
, CASE WHEN MAX(k) > MIN(k) THEN SUBSTR(text , MIN(k) + 1 , MAX(k) - MIN(k) - 1)
WHEN MAX(k) IS NULL THEN text
END AS extracted
FROM cri_customer
CROSS JOIN LATERAL
(VALUES LENGTH(text) + 1 ) f(text_len_plus)
LEFT OUTER JOIN LATERAL
(SELECT k
, ROW_NUMBER() OVER(ORDER BY k) AS rnum
FROM LATERAL
(SELECT k1 + k2 + k3 AS k /* maximun length of text = 4095 */
FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8
, 9,10,11,12,13,14,15,16 ) k(k1)
INNER JOIN
(VALUES 0, 16, 32, 48, 64, 80, 96,112
,128,144,160,176,192,208,224,240 ) k(k2)
ON k1 + k2 <= text_len_plus
INNER JOIN
(VALUES 0, 256, 512, 768,1024,1280,1536,1792
,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
ON k1 + k2 + k3 <= text_len_plus
) k
WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
)
ON MOD(rnum , 2) = 0
OR SUBSTR(text , k , 1) = ':'
CROSS JOIN LATERAL
(VALUES COALESCE( SMALLINT( (rnum + 1) / 2 ) , 1 ) ) f(line)
GROUP BY
id
, line
, text
)
SELECT id
, MAX( CASE line WHEN 1 THEN extracted END ) AS first_line
, MAX( CASE line WHEN 2 THEN extracted END ) AS second_line
, MAX( CASE line WHEN 3 THEN extracted END ) AS third_line
, MAX( CASE line WHEN 4 THEN extracted END ) AS fourth_line
, MAX( CASE line WHEN 5 THEN extracted END ) AS fifth_line
, MAX( CASE line WHEN 6 THEN extracted END ) AS sixth_line
, MAX( CASE line WHEN 7 THEN extracted END ) AS seventh_line
, MAX( CASE line WHEN 8 THEN extracted END ) AS eighth_line
, MAX( CASE line WHEN 9 THEN extracted END ) AS nineth_line
, MAX( CASE line WHEN 10 THEN extracted END ) AS tenth_line
FROM (SELECT id , line
, CAST(extracted AS VARCHAR(41) ) AS extracted
FROM elements_in_rows
)
GROUP BY
id
ORDER BY
id
;
------------------------------------------------------------------------------
ID FIRST_LINE SECOND_LINE THIRD_LINE FOURTH_LINE FIFTH_LINE SIXTH_LINE SEVENTH_LINE EIGHTH_LINE NINETH_LINE TENTH_LINE
----------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- -----------------------------------------
1 Members Tenant Not accepted due variety of Reasons Document No1 - - - - - -
101 Personal library Picture Books 1 A Kiss for Little Bear Else Homelund Minarik Maurice Sendak - - - -
102 Personal library Picture Books 2 The Moon Jumpers Janice May Udry Maurice Sendak - - - -
103 Personal library Picture Books 3 The Goodnight Gecko Gill McBarnet Gill McBarnet Published by Hawaiian publisher - - -
905 one straight line shoud display as it is. - - - - - - - - -
907 Frst line data Second line data - - - - - - - -
996 - - - - - - - - - -
997 - - - - - - - - - -
8 record(s) selected.
|
Last edited by tonkuma; 12-24-11 at 04:45.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Splitting a row in Mulitple columns
