MySQL-행에서 열로

lottoking 2020. 5. 28. 08:20

MySQL-행에서 열로

게시물을 검색하려고했지만 SQL Server / Access에 대한 솔루션 만 찾았습니다. MySQL (5.X) 솔루션이 필요합니다.

hostid, itemname, itemvalue라는 3 개의 열이있는 테이블 (히스토리라고 함)이 있습니다.
선택 ( select * from history)을하면 반환됩니다

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    c     |    40     |
   +--------+----------+-----------+

데이터베이스를 쿼리하여 다음과 같은 것을 반환하는 방법

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

이 문제를 해결하기 위해 취해야 할 단계에 대해 좀 더 길고 자세한 설명을 추가하겠습니다. 너무 길면 사과드립니다.

나는 당신이 제공 한 기초부터 시작하여이 포스트의 나머지 부분에 사용할 몇 가지 용어를 정의하는 데 사용할 것입니다. 이것은 기본 테이블입니다 .

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

이것이 우리의 목표, 예쁜 피벗 테이블입니다 .

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

의 값 history.hostid열이 될 것이다 y 값 피벗 테이블이다. 의 값 history.itemname열이 될 것입니다 x 값 (분명한 이유를).

피벗 테이블을 만드는 문제를 해결해야하는 경우 3 단계 프로세스 (선택적 4 단계)를 사용하여 문제를 해결합니다.

관심있는 열을 선택하십시오 (예 : y- 값 및 x- 값)
추가 열을 사용하여 기본 테이블을 확장-각 x 값 마다 하나씩
확장 테이블 그룹화 및 집계-각 y- 값 에 대해 하나의 그룹
(선택 사항) 집계 된 테이블을 확인

이 단계를 문제에 적용하고 우리가 얻는 것을 보자.

1 단계 : 관심의 선택 열 . 원하는 결과 hostid에서 y 값 을 itemname제공하고 x 값 을 제공 합니다 .

2 단계 : 여분의 열이 기본 테이블을 확장 . 일반적으로 x- 값당 하나의 열이 필요합니다. x- 값 열은 itemname다음과 같습니다.

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

우리는 행 수를 변경하지 않았으며 추가 열을 추가했습니다. 또한 NULLs 의 패턴 - itemname = "A"새 열에 대해서는 널이 아닌 값을 가지며 A다른 새 열에 대해서는 널값을 갖는 행에 유의하십시오 .

3 단계 : 확장 테이블을 그룹화하고 집계합니다 . group by hostidy- 값을 제공하기 때문에을 수행 해야 합니다.

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

(y- 값당 하나의 행 이 생겼습니다 .) 이제 거의 다 왔습니다 ! 우리는 그 추악한 것들을 제거해야합니다 NULL.

4 단계 : prettify . 우리는 null 값을 0으로 대체하여 결과 집합을 더 잘 볼 수 있습니다.

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

그리고 우리는 끝났습니다-우리는 MySQL을 사용하여 멋진 피벗 테이블을 만들었습니다.

이 절차를 적용 할 때 고려해야 할 사항 :

추가 열에 사용할 값 itemvalue이 예제에서 사용 했습니다
추가 열에 사용할 "중립"값 나는 사용 NULL했지만 정확한 상황에 따라 0또는 ""일 수도 있습니다.
그룹화 할 때 사용할 집계 함수 내가 사용 sum하지만, count그리고 max종종 (사용되는 max여러 행에 걸쳐 확산 있었다 하나의 행 "객체"를 구축 할 때 자주 사용된다)
y- 값에 여러 열을 사용합니다. 이 솔루션은 y 값에 단일 열을 사용하는 것으로 제한되지 않습니다-여분의 열을
group by절에 연결하십시오 (그리고 잊지 마십시오 select).

알려진 제한 사항 :

이 솔루션은 피벗 테이블에서 n 개의 열을 허용하지 않습니다. 기본 테이블을 확장 할 때 각 피벗 열을 수동으로 추가해야합니다. 따라서 5 또는 10 x 값의 경우이 솔루션이 좋습니다. 100에 대해서는 그렇게 좋지 않습니다. 쿼리를 생성하는 저장 프로 시저가있는 몇 가지 솔루션이 있지만 추악하고 제대로 이해하기가 어렵습니다. 현재 피벗 테이블에 많은 열이 필요할 때이 문제를 해결하는 좋은 방법을 모릅니다.

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

피벗해야 할 항목이 많은 경우 특히 유용한 또 다른 옵션은 mysql이 쿼리를 작성하도록하는 것입니다.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FIDDLE 작동하는지 확인하기 위해 추가 값을 추가했습니다

GROUP_CONCAT 기본값이 1000이므로 쿼리가 너무 큰 경우 실행하기 전에이 매개 변수를 변경하십시오.

SET SESSION group_concat_max_len = 1000000;

테스트:

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

  hostid    A     B     C      D
    1     10      3     0      0
    2     9       0    40      5
    3     14     67     0      8

문제를 해결하는 데 도움이 된 Matt Fenwick의 아이디어를 활용하여 (고마워) 하나의 쿼리로만 줄 이겠습니다.

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

하위 쿼리에서 Agung Sagita 의 답변을 편집 하여 참여합니다. 이 두 가지 방법의 차이점이 확실하지 않지만 다른 참조 용입니다.

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

하위 쿼리 사용

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

그러나 하위 쿼리가 행보다 많은 결과를 얻는 경우 하위 쿼리에서 추가 집계 함수를 사용하면 문제가됩니다.

나는에 있는지 확인 Group By hostId후이 값 만 첫 번째 행이 표시됩니다
등에 :

A   B  C
1  10
2      3

내 해결책 :

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

제출 된 사례에서 예상 결과를 생성합니다.

간단한 쿼리를 사용하여 행을 열로 변환하는 보고서를 거의 동적으로 만드는 한 가지 방법을 알아 냈습니다. 여기에서 온라인으로 보고 테스트 할 수 있습니다 .

쿼리 열 수 는 고정되어 있지만 값은 동적 이며 행 값을 기반으로합니다. 하나의 쿼리를 사용하여 테이블 헤더를 만들고 다른 하나를 사용하여 값을 볼 수 있습니다.

SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;

SELECT
     hostid
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
FROM history order by 1;

또한 요약 할 수 있습니다.

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

RexTester의 결과 :

http://rextester.com/ZSWKS28923

실제 사용 예 중 하나 인이 보고서는 보트 / 버스 출발 시간을 시각적 인 일정과 함께 열에 표시합니다. 당신은 시각화를 혼동하지 않고 마지막 열에서 사용되지 않은 하나의 추가 열을 볼 수 있습니다 : ** 발권 시스템 온라인 및 선물 티켓 판매

This isn't the exact answer you are looking for but it was a solution that i needed on my project and hope this helps someone. This will list 1 to n row items separated by commas. Group_Concat makes this possible in MySQL.

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

This cemetery has two common names so the names are listed in different rows connected by a single id but two name ids and the query produces something like this

CemeteryID Cemetery_Name Latitude
1 Appleton,Sulpher Springs 35.4276242832293

I'm sorry to say this and maybe I'm not solving your problem exactly but PostgreSQL is 10 years older than MySQL and is extremely advanced compared to MySQL and there's many ways to achieve this easily. Install PostgreSQL and execute this query

CREATE EXTENSION tablefunc;

then voila! And here's extensive documentation: PostgreSQL: Documentation: 9.1: tablefunc or this query

CREATE EXTENSION hstore;

then again voila! PostgreSQL: Documentation: 9.0: hstore

If you could use MariaDB there is a very very easy solution.

Since MariaDB-10.02 there has been added a new storage engine called CONNECT that can help us to convert the results of another query or table into a pivot table, just like what you want: You can have a look at the docs.

First of all install the connect storage engine.

이제 테이블의 피벗 열이 itemname있고 각 항목의 데이터가 itemvalue열에 있으므로이 쿼리를 사용하여 결과 피벗 테이블을 만들 수 있습니다.

create table pivot_table
engine=connect table_type=pivot tabname=history
option_list='PivotCol=itemname,FncCol=itemvalue';

이제 우리가 원하는 것을 선택할 수 있습니다 pivot_table:

select * from pivot_table

자세한 내용은 여기

참고 URL : https://stackoverflow.com/questions/1241178/mysql-rows-to-columns

'IT' 카테고리의 다른 글

string.isEmpty () 또는“”.equals (string)을 사용해야합니까? (0)	2020.05.28
경로에서 폴더 이름 얻기 (0)	2020.05.28
Eclipse에서 자동 정렬 바로 가기 키란 무엇입니까? (0)	2020.05.28
디렉토리에있는 확장자의 모든 파일을 gitignore (0)	2020.05.28
문자열을 텍스트 파일에 쓰고 항상 기존 내용을 덮어 씁니다. (0)	2020.05.28

현재글MySQL-행에서 열로

내가 바로 로또왕!

영화순위, 행사, spring3, DVD순위, 공연, 축제, 연극, javascript, Java, 자바, 뮤지컬, spring, jquery, C#, 관광, 놀거리, 무비순위, 여행, c++, 볼거리,

Today :
Yesterday :

lottoking