MYSQL - How do I unpivot my data to get the following output below?DB Error=ORA-01407: cannot update (“XXX”.“XXX”.“VALUE”) to NULL.How to get the following output?Materialized View involving UNION ALL operator in FROM with ON COMMITPassing Numeric Variable into Simple oracle queryHow to get output like below?how to get following output in oracle using query?Updating all rows in one table who have 1 match, more than 1 match, or 0 matches in other tableSELECT WHERE Query Spanning Numerous Rows Linked by RotationID columnIndex is not getting usedOracle index barely speeding up aggregate calculations
What kind of floor tile is this?
What are some good ways to treat frozen vegetables such that they behave like fresh vegetables when stir frying them?
How to explain what's wrong with this application of the chain rule?
awk assign to multiple variables at once
Were Persian-Median kings illiterate?
What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?
Creating two special characters
Why does the Sun have different day lengths, but not the gas giants?
What is the highest possible scrabble score for placing a single tile
Pre-mixing cryogenic fuels and using only one fuel tank
How could a planet have erratic days?
How to get directions in deep space?
Is this toilet slogan correct usage of the English language?
Taxes on Dividends in a Roth IRA
Are Captain Marvel's powers affected by Thanos breaking the Tesseract and claiming the stone?
How do you make your own symbol when Detexify fails?
How can I write humor as character trait?
US tourist/student visa
Did the UK lift the requirement for registering SIM cards?
How to preserve electronics (computers, iPads and phones) for hundreds of years
What to do when eye contact makes your coworker uncomfortable?
Is there any evidence that Cleopatra and Caesarion considered fleeing to India to escape the Romans?
C++ copy constructor called at return
Can you use Vicious Mockery to win an argument or gain favours?
MYSQL - How do I unpivot my data to get the following output below?
DB Error=ORA-01407: cannot update (“XXX”.“XXX”.“VALUE”) to NULL.How to get the following output?Materialized View involving UNION ALL operator in FROM with ON COMMITPassing Numeric Variable into Simple oracle queryHow to get output like below?how to get following output in oracle using query?Updating all rows in one table who have 1 match, more than 1 match, or 0 matches in other tableSELECT WHERE Query Spanning Numerous Rows Linked by RotationID columnIndex is not getting usedOracle index barely speeding up aggregate calculations
Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID
SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
CASE
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
ELSE 'DUE IN 4+ WEEKS'
END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot
(
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC
oracle
edited 16 hours ago
Raymond Nijland
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
add a comment |
Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID
SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
CASE
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
ELSE 'DUE IN 4+ WEEKS'
END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot
(
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC
oracle
edited 16 hours ago
Raymond Nijland
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
1
MySQL doesn't have anUNPIVOT... this looks more like oracle to me...
– Nick
16 hours ago
1
" this looks more like oracle to me." it is @Nick notice the(+)operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use(+)annymore by the way
– Raymond Nijland
16 hours ago
1
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago
add a comment |
Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID
SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
CASE
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
ELSE 'DUE IN 4+ WEEKS'
END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot
(
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC
oracle
edited 16 hours ago
Raymond Nijland
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID
SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
CASE
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS'
WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
ELSE 'DUE IN 4+ WEEKS'
END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot
(
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC
oracle
oracle
edited 16 hours ago
Raymond Nijland
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
edited 16 hours ago
Raymond Nijland
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
edited 16 hours ago
Raymond Nijland
8,88121329
edited 16 hours ago
Raymond Nijland
8,88121329
edited 16 hours ago
Raymond Nijland
8,88121329
8,88121329
asked 17 hours ago
Dean NewsteadDean Newstead
51
asked 17 hours ago
Dean NewsteadDean Newstead
51
asked 17 hours ago
Dean NewsteadDean Newstead
51
51
1
MySQL doesn't have anUNPIVOT... this looks more like oracle to me...
– Nick
16 hours ago
1
" this looks more like oracle to me." it is @Nick notice the(+)operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use(+)annymore by the way
– Raymond Nijland
16 hours ago
1
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago
add a comment |
1
MySQL doesn't have anUNPIVOT... this looks more like oracle to me...
– Nick
16 hours ago
1
" this looks more like oracle to me." it is @Nick notice the(+)operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use(+)annymore by the way
– Raymond Nijland
16 hours ago
1
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago
1
1
MySQL doesn't have an
UNPIVOT... this looks more like oracle to me...– Nick
16 hours ago
MySQL doesn't have an
UNPIVOT... this looks more like oracle to me...– Nick
16 hours ago
1
1
" this looks more like oracle to me." it is @Nick notice the
(+) operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use (+) annymore by the way– Raymond Nijland
16 hours ago
" this looks more like oracle to me." it is @Nick notice the
(+) operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use (+) annymore by the way– Raymond Nijland
16 hours ago
1
1
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago
add a comment |
0
active
oldest
votes
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%2f55279781%2fmysql-how-do-i-unpivot-my-data-to-get-the-following-output-below%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f55279781%2fmysql-how-do-i-unpivot-my-data-to-get-the-following-output-below%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
1
MySQL doesn't have an
UNPIVOT... this looks more like oracle to me...– Nick
16 hours ago
1
" this looks more like oracle to me." it is @Nick notice the
(+)operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use(+)annymore by the way– Raymond Nijland
16 hours ago
1
@RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
– Nick
16 hours ago