[SQL] 열 등의 SQL 트랜스 행

열 등의 SQL 트랜스 행

나는 순수 SQL에서 해결 될 수 있다고 생각 흥미로운 수수께끼가있다. 나는 다음과 유사한 테이블이 :

responses:

user_id | question_id | body
----------------------------
1       | 1           | Yes
2       | 1           | Yes
1       | 2           | Yes
2       | 2           | No
1       | 3           | No
2       | 3           | No


questions:

id | body
-------------------------
1 | Do you like apples?
2 | Do you like oranges?
3 | Do you like carrots?

나는 다음과 같은 출력을 좀하고 싶습니다

user_id | Do you like apples? | Do you like oranges? | Do you like carrots?
---------------------------------------------------------------------------
1       | Yes                 | Yes                  | No
2       | Yes                 | No                   | No

그래서 나는 모든 질문에 단지 코드가 할 수없는, 나는 거기에 얼마나 많은 질문 모르는, 그들은 동적 될 것입니다. 나는 PostgreSQL을 사용하고 난이 전위라고 생각하지만, 나는 SQL에서이 일을의 표준 방법을 말한다 아무것도 찾을 수없는 것. 나는 대학 내 데이터베이스 클래스 뒤에이 일을 기억하지만, MySQL의에 있었고, 우리가 그것을 어떻게 솔직히 기억하지 않습니다.

나는의 조인 조합 문 BY 그룹이 될 것입니다 있으리라 믿고있어,하지만 난 시작하는 방법을 알아낼 수 없습니다.

누구를 알고 어떻게이 일을? 매우 감사합니다!

편집 1 : 내가 원하는 것 같다 크로스 탭을 사용하는 방법에 대한 몇 가지 정보를 발견,하지만 난 그것의 문제가 만드는 감각을 보내고 있습니다. 더 나은 기사에 대한 링크는 크게 감사하겠습니다!

해결법

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

   1.사용하다:

   사용하다:

     SELECT r.user_id,
            MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS "Do you like apples?",
            MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS "Do you like oranges?",
            MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS "Do you like carrots?"
       FROM RESPONSES r
       JOIN QUESTIONS q ON q.id = r.question_id
   GROUP BY r.user_id
   

   당신이 원주 데이터 행의 데이터를 "피벗"되기 때문 표준 피벗 쿼리입니다.

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

   2.열심히 코드에 대한 답변의 특정 클래스를 가진 외부 모듈 / 확장을 사용하지 않고이 문제를 처리하기 위해 진정한 동적 기능을 구현했습니다. 또한 열 순서를 제어 할 수있게 여러 키와 클래스 / 속성 열을 지원합니다.

   열심히 코드에 대한 답변의 특정 클래스를 가진 외부 모듈 / 확장을 사용하지 않고이 문제를 처리하기 위해 진정한 동적 기능을 구현했습니다. 또한 열 순서를 제어 할 수있게 여러 키와 클래스 / 속성 열을 지원합니다.

   당신은 여기에서 찾을 수 있습니다 https://github.com/jumpstarter-io/colpivot

   이 특정 문제를 해결 예 :

   begin;
   
   create temporary table responses (
       user_id integer,
       question_id integer,
       body text
   ) on commit drop;
   
   create temporary table questions (
       id integer,
       body text
   ) on commit drop;
   
   insert into responses values (1,1,'Yes'), (2,1,'Yes'), (1,2,'Yes'), (2,2,'No'), (1,3,'No'), (2,3,'No');
   insert into questions values (1, 'Do you like apples?'), (2, 'Do you like oranges?'), (3, 'Do you like carrots?');
   
   select colpivot('_output', $$
       select r.user_id, q.body q, r.body a from responses r
           join questions q on q.id = r.question_id
   $$, array['user_id'], array['q'], '#.a', null);
   
   select * from _output;
   
   rollback;
   

   이 출력 :

    user_id | 'Do you like apples?' | 'Do you like carrots?' | 'Do you like oranges?' 
   ---------+-----------------------+------------------------+------------------------
          1 | Yes                   | No                     | Yes
          2 | Yes                   | No                     | No
   

3. ==============================

   3.이 방법으로 크로스 탭 기능이 예제를 해결할 수

   이 방법으로 크로스 탭 기능이 예제를 해결할 수

   drop table if exists responses;
   create table responses (
   user_id integer,
   question_id integer,
   body text
   );
   
   drop table if exists questions;
   create table questions (
   id integer,
   body text
   );
   
   insert into responses values (1,1,'Yes'), (2,1,'Yes'), (1,2,'Yes'), (2,2,'No'), (1,3,'No'), (2,3,'No');
   insert into questions values (1, 'Do you like apples?'), (2, 'Do you like oranges?'), (3, 'Do you like carrots?');
   
   select * from crosstab('select responses.user_id, questions.body, responses.body from responses, questions where questions.id = responses.question_id order by user_id') as ct(userid integer, "Do you like apples?" text, "Do you like oranges?" text, "Do you like carrots?" text);
   

   첫째, 당신은 tablefunc 확장을 설치해야합니다. 9.1 버전 이후로는 확장을 만들 사용하여 수행 할 수 있습니다

   CREATE EXTENSION tablefunc;
   

4. ==============================

   4.나는 동적 쿼리를 생성하는 기능을 썼다. 이는 교차 대한 SQL을 생성하고 도면을 작성 (존재하는 경우 먼저 방울). 당신은 이상의 결과를 얻을 수있는보기에서 선택할 수 있습니다.

   나는 동적 쿼리를 생성하는 기능을 썼다. 이는 교차 대한 SQL을 생성하고 도면을 작성 (존재하는 경우 먼저 방울). 당신은 이상의 결과를 얻을 수있는보기에서 선택할 수 있습니다.

   여기에 기능은 다음과 같습니다

   CREATE OR REPLACE FUNCTION public.c_crosstab (
     eavsql_inarg varchar,
     resview varchar,
     rowid varchar,
     colid varchar,
     val varchar,
     agr varchar
   )
   RETURNS void AS
   $body$
   DECLARE
       casesql varchar;
       dynsql varchar;    
       r record;
   BEGIN   
    dynsql='';
   
    for r in 
         select * from pg_views where lower(viewname) = lower(resview)
     loop
         execute 'DROP VIEW ' || resview;
     end loop;   
   
    casesql='SELECT DISTINCT ' || colid || ' AS v from (' || eavsql_inarg || ') eav ORDER BY ' || colid;
    FOR r IN EXECUTE casesql Loop
       dynsql = dynsql || ', ' || agr || '(CASE WHEN ' || colid || '=''' || r.v || ''' THEN ' || val || ' ELSE NULL END) AS ' || agr || '_' || r.v;
    END LOOP;
    dynsql = 'CREATE VIEW ' || resview || ' AS SELECT ' || rowid || dynsql || ' from (' || eavsql_inarg || ') eav GROUP BY ' || rowid;
    RAISE NOTICE 'dynsql %1', dynsql; 
    EXECUTE dynsql;
   END
   
   $body$
   LANGUAGE 'plpgsql'
   VOLATILE
   CALLED ON NULL INPUT
   SECURITY INVOKER
   COST 100;
   

   그리고 여기에 내가 그것을 사용하는 방법입니다 :

   SELECT c_crosstab('query_txt', 'view_name', 'entity_column_name', 'attribute_column_name', 'value_column_name', 'first');
   

   예: 주먹 당신은 실행 :

   SELECT c_crosstab('Select * from table', 'ct_view', 'usr_id', 'question_id', 'response_value', 'first');
   

   보다:

   Select * from ct_view;
   

5. ==============================

   5.있는 contrib / tablefunc /이의 예입니다.

   있는 contrib / tablefunc /이의 예입니다.

from https://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns by cc-by-sa and MIT license