복붙노트

[SQL] MySQL은 - 열을 행

SQL

MySQL은 - 열을 행

나는 게시물을 검색했지만, 난 단지 SQL 서버 / 액세스 솔루션을 발견했다. 내가 MySQL을 (5.X)의 솔루션이 필요합니다.

내가 3 열이있는 테이블 (라는 역사)가 : 호스트 ID를 itemName에를 itemvalue. 나는 선택 (역사에서 선택 *)를 할 경우 반환됩니다

   +--------+----------+-----------+
   | 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 |
   +--------+------+-----+-----+

해결법

  1. ==============================

    1.나는이 문제를 해결하기 위해 수행하는 단계의 다소 길고 자세한 설명을 추가 할거야. 너무 오래 있다면 나는 죄송합니다.

    나는이 문제를 해결하기 위해 수행하는 단계의 다소 길고 자세한 설명을 추가 할거야. 너무 오래 있다면 나는 죄송합니다.

    나는 당신이 준 기본으로 시작하고 나는이 게시물의 나머지 부분에 사용할 수 있습니다 용어 몇 가지를 정의하는 데 사용할 수 있습니다. 이 기본 테이블 될 것입니다 :

    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 값이 될 것입니다.

    I 피벗 테이블을 생성의 문제를 해결해야 할 때, I는 (선택적 네번째 단계)와 세 단계 과정을 사용하여 해결 :

    하자가 문제에 다음 단계를 적용하고 우리가 무엇을 얻을 참조 :

    1 단계 : 관심의 선택 열. 원하는 결과에서, 호스트 ID는 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 |
+--------+----------+-----------+------+------+------+

    우리가 행의 수를 변경하지 않은 참고 - 우리는 단지 여분의 열을 추가했다. 또한, NULL 값의 패턴을 주 - itemName에있는 행이 "A"= 다른 새 항목에 대한 새 항목 A에 대한 널이 아닌 값, 널 값을 갖는다.

    3 단계 : 그룹 및 집계 확장 테이블. 는 y 값을 제공하기 때문에 우리는 호스트 ID로 그룹에 필요

    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 단계 : 싸게 치장. 우리는 결과 세트가보고 좋네요 있도록 제로로에 null 값을 대체하는거야 :

    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을 사용 멋진, 꽤 피벗 테이블을 구축했습니다.

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

    알려진 제한 사항 :

  2. ==============================

    2.

    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;
  3. ==============================

    3.당신은 당신이 선회에 필요한 많은 항목이있는 경우 특히 유용합니다 또 다른 옵션은, MySQL의 빌드 당신을위한 질의를하도록하는 것입니다 :

    당신은 당신이 선회에 필요한 많은 항목이있는 경우 특히 유용합니다 또 다른 옵션은, 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;

    깡깡이 몇 가지 추가 값을 추가하는 작업을 볼 수

    당신이 그것을 실행하기 전에 정말 큰 쿼리 변경이 매개 변수가 그래서 만약 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
  4. ==============================

    4.문제 (덕분에 많은)의 단 하나 개의 쿼리로 감소하자를 해결하기 위해 나를 도와 매트 펜윅의 아이디어의 활용 :

    문제 (덕분에 많은)의 단 하나 개의 쿼리로 감소하자를 해결하기 위해 나를 도와 매트 펜윅의 아이디어의 활용 :

    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
  5. ==============================

    5.하위 쿼리에서 I 편집 아궁 사 기타의 대답은 가입. 나는 그러나 또 다른 참조를 위해,이 두 방법 사이에 얼마나 많은 차이점에 대해 확실하지 않다.

    하위 쿼리에서 I 편집 아궁 사 기타의 대답은 가입. 나는 그러나 또 다른 참조를 위해,이 두 방법 사이에 얼마나 많은 차이점에 대해 확실하지 않다.

    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'
  6. ==============================

    6.사용 하위 쿼리

    사용 하위 쿼리

    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

    하위 쿼리가 더 행 이상의 결과하지만 만약이 문제가 될 것입니다, 서브 쿼리에 추가 집계 함수를 사용

  7. ==============================

    7.내 솔루션 :

    내 솔루션 :

    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

    그것은 제출 된 경우에 예상되는 결과를 얻을 수 있습니다.

  8. ==============================

    8.나는 그룹으로 호스트 ID에 다음 값 만 첫 번째 행을 표시합니다 있는지 확인 처럼:

    나는 그룹으로 호스트 ID에 다음 값 만 첫 번째 행을 표시합니다 있는지 확인 처럼:

    A   B  C
1  10
2      3
  9. ==============================

    9.한 가지 방법 밖에 난 그림은 내 보고서는 간단한 querys를 사용하여 거의 동적 컬럼에 행을 변환 할 수 있습니다. 당신은보고 여기에 온라인을 테스트 할 수 있습니다.

    한 가지 방법 밖에 난 그림은 내 보고서는 간단한 querys를 사용하여 거의 동적 컬럼에 행을 변환 할 수 있습니다. 당신은보고 여기에 온라인을 테스트 할 수 있습니다.

    질의의 열 수는 고정되어 있지만, 동적 값은 행의 값에 기초한다. 당신은 그래서, 테이블 헤더 값을 볼 수있는 또 다른 하나를 구축하는 하나의 쿼리를 사용하여 구축 할 수 있습니다 :

    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

    사용 하나 실제 예를 들어, 열에서이 보고서를 넣고 쇼 시각적 일정 보트 / 버스의 출발 도착 시간. 당신은 혼동 시각화없이 마지막 COL에서 사용되지 않는 하나의 추가 열을 볼 수 있습니다 : ** 온라인 presential 판매 티켓을 티켓 시스템

  10. ==============================

    10.이것은 당신이 찾고있는 정확한 답변을하지 않습니다하지만 난 내 프로젝트에서 필요로하는 솔루션이며이 사람을 도움이되기를 바랍니다. N 행 항목이 뜻리스트 1 콤마로 분리된다. GROUP_CONCAT는 MySQL의이 가능하게합니다.

    이것은 당신이 찾고있는 정확한 답변을하지 않습니다하지만 난 내 프로젝트에서 필요로하는 솔루션이며이 사람을 도움이되기를 바랍니다. N 행 항목이 뜻리스트 1 콤마로 분리된다. GROUP_CONCAT는 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

    이 묘지는 이름 때문에 두 가지 일반적인 이름은 하나의 ID 만이 이름 식별자로 연결된 다른 행에 나와있는 쿼리는 다음과 같이 뭔가를 생산하고있다  CemeteryID Cemetery_Name 위도 1 애플 턴, Sulpher 스프링스 35.4276242832293

  11. ==============================

    11.나는이 말을 미안 해요 어쩌면 내가 정확하게 문제를 해결하고 있지 않다 그러나 PostgreSQL은 MySQL을 10 년 이상 나이가와 MySQL에 비해 매우 고급이고 쉽게 달성하는 방법에는 여러 가지가있다. PostgreSQL을 설치하고이 쿼리를 실행

    나는이 말을 미안 해요 어쩌면 내가 정확하게 문제를 해결하고 있지 않다 그러나 PostgreSQL은 MySQL을 10 년 이상 나이가와 MySQL에 비해 매우 고급이고 쉽게 달성하는 방법에는 여러 가지가있다. PostgreSQL을 설치하고이 쿼리를 실행

    CREATE EXTENSION tablefunc;

    다음 짜잔! 그리고 여기에 광범위한 문서입니다 : PostgreSQL의 : 문서 : 9.1 : tablefunc 또는이 쿼리

    CREATE EXTENSION hstore;

    다시 짜잔! PostgreSQL의 : 문서 : 9.0 : hstore

  12. ==============================

    12.당신이 MariaDB를 사용할 수 있다면 매우 매우 쉬운 솔루션이있다.

    당신이 MariaDB를 사용할 수 있다면 매우 매우 쉬운 솔루션이있다.

    MariaDB는-10.02은 당신이 원하는 것처럼, 피벗 테이블에 다른 쿼리 또는 테이블의 결과를 변환하는 데 도움이 있습니다 CONNECT라는 새로운 스토리지 엔진이 추가되었습니다 이후 : 당신은 문서를 살펴 수 있습니다.

    첫째로의 연결 스토리지 엔진을 설치합니다.

    이제 우리 테이블의 피벗 열은 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

    더 자세한 사항은 여기에

    13. from https://stackoverflow.com/questions/1241178/mysql-rows-to-columns by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의에서 순위 기능  (0) 2020.03.05
[SQL] 표현에 의해 ORA-00979 그룹에없는  (0) 2020.03.05
[SQL] 내부와 업데이트 문은 오라클에 합류  (0) 2020.03.05
[SQL] T-SQL 분할 문자열  (0) 2020.03.05
[SQL] SQL Server의 누적 합계를 계산  (0) 2020.03.05
,

티스토리툴바