데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MySQL Q&A 27995 게시물 읽기

No. 27995

문자열 분리 및 행과 열 바꾸기
작성자	초보	작성일	2009-02-03 10:58ⓒ 2009-02-03 11:05ⓜ	조회수	7,017

막 Mysql을 공부하기 시작한 학생입니다. 여기저기 돌아다니면서 방법을 찾고 있는데 쉽지가 않네요.. 그래서 이렇게 염치불구하고 질문드립니다.

현재 가지고 있는 DB의 구성은 다음과 같이 되어 있습니다.

ID NUMBER

----- ----------------

1 a(1), c(2), d(2)

2 b(2), d(1)

3 a(3), b(2), c(1), d(2)

즉 Number라는 열에 각각의 data들이 괄호 또는 ","로 구분되어 있는데 이를 분리하려고 합니다.

즉, Number열에 있는 각각의 문자와 숫자를 구분해서 새로운 열로 바꾸려고 합니다. 즉,

ID #1 #2 #3 #4 #5 #6 #7 #8

----- ------ ----- ----- ----- ----- ----- ----- -----

1 a 1 c 2 d 2

2 b 2 d 1

3 a 3 b 2 c 1 d 2

이런식으로 각각의 data를 열로 구분한 후 다시 이를 행으로 바꾸려고 합니다. 즉,

ID ID#1 ID#2

----- ----- -----

1 a 1

1 c 2

1 d 2

2 b 2

2 d 1

이와 같이 행과 열을 바꾸어서 DB에 저장하려고 하는데, 여기저기 찾아봐도 도저히 모르겠네요.. 고수님들의 답변 부탁드립니다.

정말정말 감사합니다.

이 글에 대한 댓글이 총 7건 있습니다.

반드시 MySQL 내에서만 해결해야 하나요?

다른 스크립트 언어를 통해서 수행하는 것이 가장 쉽다고 생각됩니다. 스크립트라도 필요하시다면 다시 댓글 달아주세요.

박현우(lqez)님이 2009-02-03 17:53에 작성한 댓글입니다.

박현우님,

너무너무 감사합니다. 다른 스크립트 언어라도 가능한 방법이 있으면 가르쳐 주시면 너무너무 도움이 될 것 같습니다. 꼭 답변 주세요.. 감사합니다.

초보님이 2009-02-04 10:45에 작성한 댓글입니다. Edit Ｘ

저도 초보지만 php로 한 번 만들어 보았습니다.

소스는 가장 아래에 있고, 실행 결과는 다음과 같습니다.

mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| id_1 | varchar(255) | NO   |     | NULL    |       |
| id_2 | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+----+------------------------+
| id | number                 |
+----+------------------------+
| 1 | a(1), c(2), d(2)       |
| 2 | b(2), d(1)             |
| 3 | a(3), b(2), c(1), d(2) |
+----+------------------------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+------+
| id | id_1 | id_2 |
+----+------+------+
| 1 | a    |    1 |
| 1 | c    |    2 |
| 1 | d    |    2 |
| 2 | b    |    2 |
| 2 | d    |    1 |
| 3 | a    |    3 |
| 3 | b    |    2 |
| 3 | c    |    1 |
| 3 | d    |    2 |
+----+------+------+
9 rows in set (0.00 sec)

mysql>

$conn = mysql_connect(...);
mysql_select_db(...);

// GET ALL DATA FROM SOURCE TABLE
$src = array();
$q = mysql_query('SELECT * FROM `t1`');
while( $d = mysql_fetch_row( $q ) )
$src[$d[0]] = explode( ',', $d[1] );

// PARSE THEM, AND PUSH INTO ARRAY
$dst = array();
foreach( $src as $id => $values )
foreach( $values as $v )
{
  preg_match( '/(?<id1>\w+)$(?<id2>\d+)$/', $v, $matches );
  $dst[] = array(
   "id" => $id,
   "id1" => "'${matches['id1']}'",
   "id2" => $matches['id2']
  );
}

// CHECK RESULT
print_r( $dst );

// INSERT INTO DATABASE
foreach( $dst as $d )
mysql_query( 'INSERT INTO `t2` values('.implode(',',$d).')' );

mysql_close( $conn );

박현우(lqez)님이 2009-02-04 16:51에 작성한 댓글입니다.

# 사전준비작업

create table copy_t
(
    no     int(2) unsigned    not null    primary key,
    cno    varchar(2)         not null
);

insert into copy_t
select 1 no, '01' cno union all
select 2 no, '02' cno union all
select 3 no, '03' cno union all
select 4 no, '04' cno union all
select 5 no, '05' cno union all
select 6 no, '06' cno union all
select 7 no, '07' cno union all
select 8 no, '08' cno union all
select 9 no, '09' cno union all
select 10 no, '10' cno union all
select 11 no, '11' cno union all
select 12 no, '12' cno union all
select 13 no, '13' cno union all
select 14 no, '14' cno union all
select 15 no, '15' cno union all
select 16 no, '16' cno union all
select 17 no, '17' cno union all
select 18 no, '18' cno union all
select 19 no, '19' cno union all
select 20 no, '20' cno union all
select 21 no, '21' cno union all
select 22 no, '22' cno union all
select 23 no, '23' cno union all
select 24 no, '24' cno union all
select 25 no, '25' cno union all
select 26 no, '26' cno union all
select 27 no, '27' cno union all
select 28 no, '28' cno union all
select 29 no, '29' cno union all
select 30 no, '30' cno union all
select 31 no, '31' cno union all
select 32 no, '32' cno union all
select 33 no, '33' cno union all
select 34 no, '34' cno union all
select 35 no, '35' cno union all
select 36 no, '36' cno union all
select 37 no, '37' cno union all
select 38 no, '38' cno union all
select 39 no, '39' cno union all
select 40 no, '40' cno union all
select 41 no, '41' cno union all
select 42 no, '42' cno union all
select 43 no, '43' cno union all
select 44 no, '44' cno union all
select 45 no, '45' cno union all
select 46 no, '46' cno union all
select 47 no, '47' cno union all
select 48 no, '48' cno union all
select 49 no, '49' cno union all
select 50 no, '50' cno union all
select 51 no, '51' cno union all
select 52 no, '52' cno union all
select 53 no, '53' cno union all
select 54 no, '54' cno union all
select 55 no, '55' cno union all
select 56 no, '56' cno union all
select 57 no, '57' cno union all
select 58 no, '58' cno union all
select 59 no, '59' cno union all
select 60 no, '60' cno union all
select 61 no, '61' cno union all
select 62 no, '62' cno union all
select 63 no, '63' cno union all
select 64 no, '64' cno union all
select 65 no, '65' cno union all
select 66 no, '66' cno union all
select 67 no, '67' cno union all
select 68 no, '68' cno union all
select 69 no, '69' cno union all
select 70 no, '70' cno union all
select 71 no, '71' cno union all
select 72 no, '72' cno union all
select 73 no, '73' cno union all
select 74 no, '74' cno union all
select 75 no, '75' cno union all
select 76 no, '76' cno union all
select 77 no, '77' cno union all
select 78 no, '78' cno union all
select 79 no, '79' cno union all
select 80 no, '80' cno union all
select 81 no, '81' cno union all
select 82 no, '82' cno union all
select 83 no, '83' cno union all
select 84 no, '84' cno union all
select 85 no, '85' cno union all
select 86 no, '86' cno union all
select 87 no, '87' cno union all
select 88 no, '88' cno union all
select 89 no, '89' cno union all
select 90 no, '90' cno union all
select 91 no, '91' cno union all
select 92 no, '92' cno union all
select 93 no, '93' cno union all
select 94 no, '94' cno union all
select 95 no, '95' cno union all
select 96 no, '96' cno union all
select 97 no, '97' cno union all
select 98 no, '98' cno union all
select 99 no, '99' cno;

# 쿼리

select id,
       substr(str, 1, locate('(', str) - 1) id1,
       substr(str, locate('(', str) + 1, locate(')', str) - locate('(', str) - 1) id2
from (
        select id, number, substr(number, pos1, pos2 - pos1 + 1) str
          from (
                select id, number,
                       length(substring_index(concat(',',number), ',', no)) + 1 pos1,
                       length(substring_index(number, ',', no)) pos2
                  from (
                        select 1 id, 'a(1),c(2),d(2)'      number union all
                        select 2 id, 'b(2),d(1)'           number union all
                        select 3 id, 'a(3),b(2),c(1),d(2)' number
                       ) a,
                       copy_t b
                 where b.no <= length(number) - length(replace(number, ',', '')) + 1
               ) x
       ) y;

구헌수(madcat)님이 2009-02-04 19:03에 작성한 댓글입니다.

다들 너무너무 감사합니다. 당장 실행해보려고 합니다. 다시 한 번 너무 감사드립니다.

초보님이 2009-02-05 15:09에 작성한 댓글입니다. Edit Ｘ

구헌수님의 방법 "짱" 멋집니다. *^_^*

우욱님이 2009-02-11 08:41에 작성한 댓글입니다. Edit Ｘ

이문장들은 한개당 1바이트씩이잔하요

만약

홍길동,유관순,김길동,Juergen,홍반장

이라는 문장을 ,로 나누려 하면 문제가 되거든요...

어떻게 해요?

=======

해결했습니다.

위의 퀄이에서
length대신에 char_length 를 쓰면 되더군요~ ㅎㅎ

감사합니다~

김효선님이 2009-05-19 23:58에 작성한 댓글입니다.
이 댓글은 2009-05-20 10:19에 마지막으로 수정되었습니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
28000	More than one INDEX key was created for column '컬럼명' [1]	이득술	2009-02-05	5317
27997	테이블 스토리지 엔진을 모두 InnoDB로 선택해도 문제 없을까요? [2]	김승동	2009-02-03	5367
27996	table join 시 난해한 쿼리 작성... [1]	이민호	2009-02-03	5036
27995	문자열 분리 및 행과 열 바꾸기 [7]	초보	2009-02-03	7017
27994	mysql 오류나는부분 수정을 어떻게 해야하나요?<초짜라서여> [7]	김정훈	2009-02-02	11211
27993	mysql데이터 삭제하여도 번호가 이여져서 나와요...ㅠㅠ [3]	허점강	2009-02-02	5390
27992	mysql 에러가나도 강제로 복구시키는방법은없나요?[초짜에여] [1]	김정훈	2009-02-02	7445