SQL ORACLE converting columns into rows
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have the result with the columns Indice,valeur1 etc:
Indice value1 value2 value3 value4
1 x maths physics chemistry
2 21/01/18 133 184 115
I would like to display the result in a vertical manner like
Indice,1,2
valeur1, x,21/01/2018
valeur2,maths, 133
valeur3,physics,184
valeur4,chemistry ,115
...
...
...
...
My query:
select
'x' as Valeur1,
'maths' as Valeur2,
'physics' as Valeur3,
'Chemistry' as Valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from dual
union
select
2 as indice,
to_char((x) ) as Valeur1,
to_char(sum(maths)) as valeur2,
to_char(sum(physics)) as valeur3,
to_char(sum(chemisty)) as valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from t1
I would like to transpose the columns into rows in the result.
Could you please suggest me the possible solutions.
sql oracle pivot unpivot
add a comment |
I have the result with the columns Indice,valeur1 etc:
Indice value1 value2 value3 value4
1 x maths physics chemistry
2 21/01/18 133 184 115
I would like to display the result in a vertical manner like
Indice,1,2
valeur1, x,21/01/2018
valeur2,maths, 133
valeur3,physics,184
valeur4,chemistry ,115
...
...
...
...
My query:
select
'x' as Valeur1,
'maths' as Valeur2,
'physics' as Valeur3,
'Chemistry' as Valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from dual
union
select
2 as indice,
to_char((x) ) as Valeur1,
to_char(sum(maths)) as valeur2,
to_char(sum(physics)) as valeur3,
to_char(sum(chemisty)) as valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from t1
I would like to transpose the columns into rows in the result.
Could you please suggest me the possible solutions.
sql oracle pivot unpivot
add a comment |
I have the result with the columns Indice,valeur1 etc:
Indice value1 value2 value3 value4
1 x maths physics chemistry
2 21/01/18 133 184 115
I would like to display the result in a vertical manner like
Indice,1,2
valeur1, x,21/01/2018
valeur2,maths, 133
valeur3,physics,184
valeur4,chemistry ,115
...
...
...
...
My query:
select
'x' as Valeur1,
'maths' as Valeur2,
'physics' as Valeur3,
'Chemistry' as Valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from dual
union
select
2 as indice,
to_char((x) ) as Valeur1,
to_char(sum(maths)) as valeur2,
to_char(sum(physics)) as valeur3,
to_char(sum(chemisty)) as valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from t1
I would like to transpose the columns into rows in the result.
Could you please suggest me the possible solutions.
sql oracle pivot unpivot
I have the result with the columns Indice,valeur1 etc:
Indice value1 value2 value3 value4
1 x maths physics chemistry
2 21/01/18 133 184 115
I would like to display the result in a vertical manner like
Indice,1,2
valeur1, x,21/01/2018
valeur2,maths, 133
valeur3,physics,184
valeur4,chemistry ,115
...
...
...
...
My query:
select
'x' as Valeur1,
'maths' as Valeur2,
'physics' as Valeur3,
'Chemistry' as Valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from dual
union
select
2 as indice,
to_char((x) ) as Valeur1,
to_char(sum(maths)) as valeur2,
to_char(sum(physics)) as valeur3,
to_char(sum(chemisty)) as valeur4,
null as Valeur5,
null as Valeur6,
null as Valeur7,
null as Valeur8,
null as Valeur9,
null as Valeur10
from t1
I would like to transpose the columns into rows in the result.
Could you please suggest me the possible solutions.
sql oracle pivot unpivot
sql oracle pivot unpivot
edited Nov 26 '18 at 15:07
Manoj Rojen Muthuraja
asked Nov 25 '18 at 9:16
Manoj Rojen MuthurajaManoj Rojen Muthuraja
13
13
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You need UNPIVOT
.
select * from unpivotdemo;
INDICE VALEUR1 VALEUR2 VALEUR3 VALEUR4 VALEUR5 VALEUR6 VALEUR7 VALEUR8 VALEUR9 VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 Semaine courante Semaine precedente Semaine -2
2 03/10/18 184 115
select indice, valeur, description
from unpivotdemo
unpivot
( description for valeur in
( valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
INDICE VALEUR DESCRIPTION
---------- -------- --------------------
1 VALEUR1 Semaine courante
1 VALEUR2 Semaine precedente
1 VALEUR3 Semaine -2
2 VALEUR1 03/10/18
2 VALEUR2 184
2 VALEUR3 115
6 rows selected
By default null values are excluded. You can include them by adding include nulls
after the unpivot
keyword.
To include INDICE
in the VALEUR
column, you can move it from the select
list to the unpivot ... in
list - however you also need to convert the datatype to match:
select valeur, description
from ( select to_char(indice) as indice
, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
from unpivotdemo d )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- ----------------------------------------
INDICE 1
VALEUR1 Semaine courante
VALEUR2 Semaine precedente
VALEUR3 Semaine -2
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
INDICE 2
VALEUR1 03/10/18
VALEUR2 184
VALEUR3 115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
22 rows selected
Edit following expanded question:
To aggregate the values into comma-separated strings, you can use LISTAGG
:
select valeur, description
from ( select listagg(indice, ',') within group (order by indice) as indice
, listagg(valeur1, ',') within group (order by indice) as valeur1
, listagg(valeur2, ',') within group (order by indice) as valeur2
, listagg(valeur3, ',') within group (order by indice) as valeur3
, listagg(valeur4, ',') within group (order by indice) as valeur4
, listagg(valeur5, ',') within group (order by indice) as valeur5
, listagg(valeur6, ',') within group (order by indice) as valeur6
, listagg(valeur7, ',') within group (order by indice) as valeur7
, listagg(valeur8, ',') within group (order by indice) as valeur8
, listagg(valeur9, ',') within group (order by indice) as valeur9
, listagg(valeur10, ',') within group (order by indice) as valeur10
from unpivotdemo )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE 1,2
VALEUR1 Semaine courante,03/10/18
VALEUR2 Semaine precedente,184
VALEUR3 Semaine -2,115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
11 rows selected
Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
add a comment |
You need to use something like
select * from (
select indice from table union all
select value1 from table union all
select value2....union all
select last from table
)
Or else make use of unpivot
as
select column from table
unpivot ( column for column in (indice, value1, ... valuen) )
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53466122%2fsql-oracle-converting-columns-into-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need UNPIVOT
.
select * from unpivotdemo;
INDICE VALEUR1 VALEUR2 VALEUR3 VALEUR4 VALEUR5 VALEUR6 VALEUR7 VALEUR8 VALEUR9 VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 Semaine courante Semaine precedente Semaine -2
2 03/10/18 184 115
select indice, valeur, description
from unpivotdemo
unpivot
( description for valeur in
( valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
INDICE VALEUR DESCRIPTION
---------- -------- --------------------
1 VALEUR1 Semaine courante
1 VALEUR2 Semaine precedente
1 VALEUR3 Semaine -2
2 VALEUR1 03/10/18
2 VALEUR2 184
2 VALEUR3 115
6 rows selected
By default null values are excluded. You can include them by adding include nulls
after the unpivot
keyword.
To include INDICE
in the VALEUR
column, you can move it from the select
list to the unpivot ... in
list - however you also need to convert the datatype to match:
select valeur, description
from ( select to_char(indice) as indice
, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
from unpivotdemo d )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- ----------------------------------------
INDICE 1
VALEUR1 Semaine courante
VALEUR2 Semaine precedente
VALEUR3 Semaine -2
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
INDICE 2
VALEUR1 03/10/18
VALEUR2 184
VALEUR3 115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
22 rows selected
Edit following expanded question:
To aggregate the values into comma-separated strings, you can use LISTAGG
:
select valeur, description
from ( select listagg(indice, ',') within group (order by indice) as indice
, listagg(valeur1, ',') within group (order by indice) as valeur1
, listagg(valeur2, ',') within group (order by indice) as valeur2
, listagg(valeur3, ',') within group (order by indice) as valeur3
, listagg(valeur4, ',') within group (order by indice) as valeur4
, listagg(valeur5, ',') within group (order by indice) as valeur5
, listagg(valeur6, ',') within group (order by indice) as valeur6
, listagg(valeur7, ',') within group (order by indice) as valeur7
, listagg(valeur8, ',') within group (order by indice) as valeur8
, listagg(valeur9, ',') within group (order by indice) as valeur9
, listagg(valeur10, ',') within group (order by indice) as valeur10
from unpivotdemo )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE 1,2
VALEUR1 Semaine courante,03/10/18
VALEUR2 Semaine precedente,184
VALEUR3 Semaine -2,115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
11 rows selected
Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
add a comment |
You need UNPIVOT
.
select * from unpivotdemo;
INDICE VALEUR1 VALEUR2 VALEUR3 VALEUR4 VALEUR5 VALEUR6 VALEUR7 VALEUR8 VALEUR9 VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 Semaine courante Semaine precedente Semaine -2
2 03/10/18 184 115
select indice, valeur, description
from unpivotdemo
unpivot
( description for valeur in
( valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
INDICE VALEUR DESCRIPTION
---------- -------- --------------------
1 VALEUR1 Semaine courante
1 VALEUR2 Semaine precedente
1 VALEUR3 Semaine -2
2 VALEUR1 03/10/18
2 VALEUR2 184
2 VALEUR3 115
6 rows selected
By default null values are excluded. You can include them by adding include nulls
after the unpivot
keyword.
To include INDICE
in the VALEUR
column, you can move it from the select
list to the unpivot ... in
list - however you also need to convert the datatype to match:
select valeur, description
from ( select to_char(indice) as indice
, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
from unpivotdemo d )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- ----------------------------------------
INDICE 1
VALEUR1 Semaine courante
VALEUR2 Semaine precedente
VALEUR3 Semaine -2
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
INDICE 2
VALEUR1 03/10/18
VALEUR2 184
VALEUR3 115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
22 rows selected
Edit following expanded question:
To aggregate the values into comma-separated strings, you can use LISTAGG
:
select valeur, description
from ( select listagg(indice, ',') within group (order by indice) as indice
, listagg(valeur1, ',') within group (order by indice) as valeur1
, listagg(valeur2, ',') within group (order by indice) as valeur2
, listagg(valeur3, ',') within group (order by indice) as valeur3
, listagg(valeur4, ',') within group (order by indice) as valeur4
, listagg(valeur5, ',') within group (order by indice) as valeur5
, listagg(valeur6, ',') within group (order by indice) as valeur6
, listagg(valeur7, ',') within group (order by indice) as valeur7
, listagg(valeur8, ',') within group (order by indice) as valeur8
, listagg(valeur9, ',') within group (order by indice) as valeur9
, listagg(valeur10, ',') within group (order by indice) as valeur10
from unpivotdemo )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE 1,2
VALEUR1 Semaine courante,03/10/18
VALEUR2 Semaine precedente,184
VALEUR3 Semaine -2,115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
11 rows selected
Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
add a comment |
You need UNPIVOT
.
select * from unpivotdemo;
INDICE VALEUR1 VALEUR2 VALEUR3 VALEUR4 VALEUR5 VALEUR6 VALEUR7 VALEUR8 VALEUR9 VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 Semaine courante Semaine precedente Semaine -2
2 03/10/18 184 115
select indice, valeur, description
from unpivotdemo
unpivot
( description for valeur in
( valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
INDICE VALEUR DESCRIPTION
---------- -------- --------------------
1 VALEUR1 Semaine courante
1 VALEUR2 Semaine precedente
1 VALEUR3 Semaine -2
2 VALEUR1 03/10/18
2 VALEUR2 184
2 VALEUR3 115
6 rows selected
By default null values are excluded. You can include them by adding include nulls
after the unpivot
keyword.
To include INDICE
in the VALEUR
column, you can move it from the select
list to the unpivot ... in
list - however you also need to convert the datatype to match:
select valeur, description
from ( select to_char(indice) as indice
, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
from unpivotdemo d )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- ----------------------------------------
INDICE 1
VALEUR1 Semaine courante
VALEUR2 Semaine precedente
VALEUR3 Semaine -2
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
INDICE 2
VALEUR1 03/10/18
VALEUR2 184
VALEUR3 115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
22 rows selected
Edit following expanded question:
To aggregate the values into comma-separated strings, you can use LISTAGG
:
select valeur, description
from ( select listagg(indice, ',') within group (order by indice) as indice
, listagg(valeur1, ',') within group (order by indice) as valeur1
, listagg(valeur2, ',') within group (order by indice) as valeur2
, listagg(valeur3, ',') within group (order by indice) as valeur3
, listagg(valeur4, ',') within group (order by indice) as valeur4
, listagg(valeur5, ',') within group (order by indice) as valeur5
, listagg(valeur6, ',') within group (order by indice) as valeur6
, listagg(valeur7, ',') within group (order by indice) as valeur7
, listagg(valeur8, ',') within group (order by indice) as valeur8
, listagg(valeur9, ',') within group (order by indice) as valeur9
, listagg(valeur10, ',') within group (order by indice) as valeur10
from unpivotdemo )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE 1,2
VALEUR1 Semaine courante,03/10/18
VALEUR2 Semaine precedente,184
VALEUR3 Semaine -2,115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
11 rows selected
Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.
You need UNPIVOT
.
select * from unpivotdemo;
INDICE VALEUR1 VALEUR2 VALEUR3 VALEUR4 VALEUR5 VALEUR6 VALEUR7 VALEUR8 VALEUR9 VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 Semaine courante Semaine precedente Semaine -2
2 03/10/18 184 115
select indice, valeur, description
from unpivotdemo
unpivot
( description for valeur in
( valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
INDICE VALEUR DESCRIPTION
---------- -------- --------------------
1 VALEUR1 Semaine courante
1 VALEUR2 Semaine precedente
1 VALEUR3 Semaine -2
2 VALEUR1 03/10/18
2 VALEUR2 184
2 VALEUR3 115
6 rows selected
By default null values are excluded. You can include them by adding include nulls
after the unpivot
keyword.
To include INDICE
in the VALEUR
column, you can move it from the select
list to the unpivot ... in
list - however you also need to convert the datatype to match:
select valeur, description
from ( select to_char(indice) as indice
, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
from unpivotdemo d )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- ----------------------------------------
INDICE 1
VALEUR1 Semaine courante
VALEUR2 Semaine precedente
VALEUR3 Semaine -2
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
INDICE 2
VALEUR1 03/10/18
VALEUR2 184
VALEUR3 115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
22 rows selected
Edit following expanded question:
To aggregate the values into comma-separated strings, you can use LISTAGG
:
select valeur, description
from ( select listagg(indice, ',') within group (order by indice) as indice
, listagg(valeur1, ',') within group (order by indice) as valeur1
, listagg(valeur2, ',') within group (order by indice) as valeur2
, listagg(valeur3, ',') within group (order by indice) as valeur3
, listagg(valeur4, ',') within group (order by indice) as valeur4
, listagg(valeur5, ',') within group (order by indice) as valeur5
, listagg(valeur6, ',') within group (order by indice) as valeur6
, listagg(valeur7, ',') within group (order by indice) as valeur7
, listagg(valeur8, ',') within group (order by indice) as valeur8
, listagg(valeur9, ',') within group (order by indice) as valeur9
, listagg(valeur10, ',') within group (order by indice) as valeur10
from unpivotdemo )
unpivot include nulls
( description for valeur in
( indice
, valeur1
, valeur2
, valeur3
, valeur4
, valeur5
, valeur6
, valeur7
, valeur8
, valeur9
, valeur10 ) );
VALEUR DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE 1,2
VALEUR1 Semaine courante,03/10/18
VALEUR2 Semaine precedente,184
VALEUR3 Semaine -2,115
VALEUR4
VALEUR5
VALEUR6
VALEUR7
VALEUR8
VALEUR9
VALEUR10
11 rows selected
Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.
edited Nov 25 '18 at 11:24
answered Nov 25 '18 at 10:33
William RobertsonWilliam Robertson
8,57632233
8,57632233
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
add a comment |
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .
– Manoj Rojen Muthuraja
Nov 26 '18 at 8:54
add a comment |
You need to use something like
select * from (
select indice from table union all
select value1 from table union all
select value2....union all
select last from table
)
Or else make use of unpivot
as
select column from table
unpivot ( column for column in (indice, value1, ... valuen) )
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
add a comment |
You need to use something like
select * from (
select indice from table union all
select value1 from table union all
select value2....union all
select last from table
)
Or else make use of unpivot
as
select column from table
unpivot ( column for column in (indice, value1, ... valuen) )
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
add a comment |
You need to use something like
select * from (
select indice from table union all
select value1 from table union all
select value2....union all
select last from table
)
Or else make use of unpivot
as
select column from table
unpivot ( column for column in (indice, value1, ... valuen) )
You need to use something like
select * from (
select indice from table union all
select value1 from table union all
select value2....union all
select last from table
)
Or else make use of unpivot
as
select column from table
unpivot ( column for column in (indice, value1, ... valuen) )
edited Nov 25 '18 at 10:46
William Robertson
8,57632233
8,57632233
answered Nov 25 '18 at 9:48
Himanshu AhujaHimanshu Ahuja
1,0312219
1,0312219
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
add a comment |
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
@William Robertson Thanks for the edit :)
– Himanshu Ahuja
Nov 25 '18 at 10:48
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53466122%2fsql-oracle-converting-columns-into-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown