Thanks for the question, Ertugrul.

Asked: January 14, 2019 - 11:20 am UTC

Last updated: January 14, 2019 - 11:33 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I try to transpose multiple columns but i couldn't.

Here is my table. This data create automatically.

BODY_ID EQUPMENT DATA_NO DATA_TYPE RESULT DATA PRG                 DATETIME
52972 t000001119 1 1         @ 381 1120000000000000 14.01.2019 14:00
52972 t000001119 1 2         @ 0 1120000000000000 14.01.2019 14:00
52972 t000001119 1 3         @ 406,1 1120000000000000 14.01.2019 14:00
52972 t000001119 1 4         @ 1,08 1120000000000000 14.01.2019 14:00
52972 t000001119 2 1         @ 364 1120000000000000 14.01.2019 14:00
52972 t000001119 2 2         @ 2,2 1120000000000000 14.01.2019 14:00
52972 t000001119 2 3         @ 308,3 1120000000000000 14.01.2019 14:00
52972 t000001119 2 4         @ 5,47 1120000000000000 14.01.2019 14:00
52972 t000001119 3 1         @ 373 1120000000000000 14.01.2019 14:00
52972 t000001119 3 2         G 1,7 1120000000000000 14.01.2019 14:00
52972 t000001119 3 3         I 165,4 1120000000000000 14.01.2019 14:00
52972 t000001119 3 4         @ 2,32 1120000000000000 14.01.2019 14:00
53122 t000001186 1 1         @ 465 1000000000000000 14.01.2019 14:01
53122 t000001186 1 2         % 1,1 1000000000000000 14.01.2019 14:01
53122 t000001186 1 3         @ 293 1000000000000000 14.01.2019 14:01
53122 t000001186 1 4         @ 2,5 1000000000000000 14.01.2019 14:01

I want to transpose some rows to columns according to DATA_TYPE. For example:

BODY_ID EQUPMENT DATA_NO DATA_TYPE1 DATA_TYPE2 DATA_TYPE3 DATA_TYPE4 RESULT1 RESULT2 RESULT3 RESULT4 PRG   DATETIME
52972 t000001119 1 381  0  406,1  1,08   @ @ @ @ 1120000000000000 14.01.2019 14:00
52972 t000001119 2 364  2,2  308,3  5,47   @ G I @ 1120000000000000 14.01.2019 14:00
52972 t000001119 2 373  1,7  165,4  2,32   @ @ @ @ 1120000000000000 14.01.2019 14:00
53122 t000001186 1 465  1,1  293  2,5   @ % @ @ 1120000000000000 14.01.2019 14:00

and Chris said...

If you want to pivot many values, add another aggregate to the pivot clause for each one you want to add:

create table t as 
  select level c1, chr ( level + 64 ) c2,
         mod ( level, 3 ) c3
  from   dual
  connect by level <= 10;
  
select * from t
pivot (
  max ( c1 ) c1, max ( c2 ) c2 for c3 in ( 0, 1, 2 )
);

0_C1   0_C2   1_C1   1_C2   2_C1   2_C2   
     9 I          10 J           8 H

Note you need to alias all but one of the functions for this to work.

You can read more about this at: https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

PS - I'm struggling to understand your data.

Please supply data in the form of:

- create table + inserts

This makes it much easier to follow!

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I try your code

Ertugrul AVCI, January 14, 2019 - 11:58 am UTC

Here is create and inserts

</>
CREATE TABLE DENEME
(
BODY_ID NUMBER(10) NOT NULL,
EQUIPMENT VARCHAR2(50) NOT NULL,
DATA_NO NUMBER(10) NOT NULL,
DATA_TYPE VARCHAR2(50) NOT NULL,
RESULT VARCHAR2(5) NOT NULL,
DATA NUMBER(10,2) NOT NULL,
PRG VARCHAR2(50) NOT NULL,
DATETIME DATE
);

INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 1, '1', '@', 381, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 1, '2', '@', 0, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 1, '3', '@', 406.1, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 1, '4', '@', 1.08, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 2, '1', '@', 364, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 2, '2', '@', 2.2, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 2, '3', '@', 308.3, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 2, '4', '@', 5.47, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 3, '1', '@', 373, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 3, '2', 'G', 1.7, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 3, '3', 'I', 165.4, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (52972, 't000001119', 3, '4', '@', 2.32, '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (53122, 't000001186', 1, '1', '@', 465, '1000000000000000', TO_DATE('2018-01-14 14:01:48', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (53122, 't000001186', 1, '2', '%', 1.1, '1000000000000000', TO_DATE('2018-01-14 14:01:48', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (53122, 't000001186', 1, '3', '@', 293, '1000000000000000', TO_DATE('2018-01-14 14:01:48', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME (BODY_ID, EQUIPMENT, DATA_NO, DATA_TYPE, RESULT, DATA, PRG, DATETIME) VALUES (53122, 't000001186', 1, '4', '@', 2.5, '1000000000000000', TO_DATE('2018-01-14 14:01:48', 'YYYY-MM-DD HH24:MI:SS'));
</>

I want to this

</>
CREATE TABLE DENEME2
(
BODY_ID NUMBER(10) NOT NULL,
EQUIPMENT VARCHAR2(50) NOT NULL,
DATA_NO NUMBER(10) NOT NULL,
DATA1 NUMBER(10,2) NOT NULL,
DATA2 NUMBER(10,2) NOT NULL,
DATA3 NUMBER(10,2) NOT NULL,
DATA4 NUMBER(10,2) NOT NULL,
RESULT1 VARCHAR2(5) NOT NULL,
RESULT2 VARCHAR2(5) NOT NULL,
RESULT3 VARCHAR2(5) NOT NULL,
RESULT4 VARCHAR2(5) NOT NULL,
PRG VARCHAR2(50) NOT NULL,
DATETIME DATE
);

INSERT INTO DENEME2 (BODY_ID, EQUIPMENT, DATA_NO, DATA1, DATA2, DATA2, DATA4, RESULT1, RESULT2, RESULT3, RESULT4, PRG, DATETIME) VALUES (52972, 't000001119', 1, 381, 0, 406.1, 1.08, '@', '@', '@', '@', '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME2 (BODY_ID, EQUIPMENT, DATA_NO, DATA1, DATA2, DATA2, DATA4, RESULT1, RESULT2, RESULT3, RESULT4, PRG, DATETIME) VALUES (52972, 't000001119', 2, 364, 2.2, 308.3, 5.47, '@', '@', '@', '@', '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME2 (BODY_ID, EQUIPMENT, DATA_NO, DATA1, DATA2, DATA2, DATA4, RESULT1, RESULT2, RESULT3, RESULT4, PRG, DATETIME) VALUES (52972, 't000001119', 3, 373, 1.7, 165.4, 2.32, '@', 'G', 'I', '@', '1120000000000000', TO_DATE('2018-01-14 14:00:38', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO DENEME2 (BODY_ID, EQUIPMENT, DATA_NO, DATA1, DATA2, DATA2, DATA4, RESULT1, RESULT2, RESULT3, RESULT4, PRG, DATETIME) VALUES (53122, 't000001186', 1, 465, 1.1, 293, 2.5, '@', '%', '@', '@', '1000000000000000', TO_DATE('2018-01-14 14:01:48', 'YYYY-MM-DD HH24:MI:SS'));

</>

please help me!

Thank you

Ertugrul AVCI, January 14, 2019 - 2:42 pm UTC

I get it now. thank you very much

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

I try your code

Thank you

More to Explore

SQL

Pivot or group (multiple columns transpose)

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

I try your code

Thank you

More to Explore

SQL