Segregate column to muti-columns based on datetime

up vote
-1
down vote

favorite

I have the following dataset

TRAN_DT;               CONENT; TYPE

01/01/2018 12:00:00;   AAA   ; 1

01/01/2018 12:00:00;   AAA   ; 2

01/01/2018 12:00:00;   AAA   ; 3

01/01/2018 01:00:00;   FFF   ; 1

01/01/2018 01:00:00;   FFF   ; 2

I need my result to be like

    01/01/2018 12:00:00;01/01/2018 01:00:00

  1 AAA                ;FFF 

  2 AAA                ;FFF

  3 AAA

Thank you.

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

add a comment |

up vote
-1
down vote

favorite

I have the following dataset

TRAN_DT;               CONENT; TYPE

01/01/2018 12:00:00;   AAA   ; 1

01/01/2018 12:00:00;   AAA   ; 2

01/01/2018 12:00:00;   AAA   ; 3

01/01/2018 01:00:00;   FFF   ; 1

01/01/2018 01:00:00;   FFF   ; 2

I need my result to be like

    01/01/2018 12:00:00;01/01/2018 01:00:00

  1 AAA                ;FFF 

  2 AAA                ;FFF

  3 AAA

Thank you.

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

add a comment |

up vote
-1
down vote

favorite

I have the following dataset

TRAN_DT;               CONENT; TYPE

01/01/2018 12:00:00;   AAA   ; 1

01/01/2018 12:00:00;   AAA   ; 2

01/01/2018 12:00:00;   AAA   ; 3

01/01/2018 01:00:00;   FFF   ; 1

01/01/2018 01:00:00;   FFF   ; 2

I need my result to be like

    01/01/2018 12:00:00;01/01/2018 01:00:00

  1 AAA                ;FFF 

  2 AAA                ;FFF

  3 AAA

Thank you.

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

I have the following dataset

TRAN_DT;               CONENT; TYPE

01/01/2018 12:00:00;   AAA   ; 1

01/01/2018 12:00:00;   AAA   ; 2

01/01/2018 12:00:00;   AAA   ; 3

01/01/2018 01:00:00;   FFF   ; 1

01/01/2018 01:00:00;   FFF   ; 2

I need my result to be like

    01/01/2018 12:00:00;01/01/2018 01:00:00

  1 AAA                ;FFF 

  2 AAA                ;FFF

  3 AAA

Thank you.

sql oracle

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

edited Nov 8 at 13:34

a_horse_with_no_name

285k45428526

asked Nov 8 at 11:29

Nawaf

1411211

asked Nov 8 at 11:29

Nawaf

1411211

asked Nov 8 at 11:29

Nawaf

1411211

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

I think you want conditional aggregation:

select type, 

       max(case when tran_dt = '01/01/2018 12:00:00' then conent end) as c_20180101_12,

       max(case when tran_dt = '01/01/2018 01:00:00' then conent end) as c_20180101_01

from t

group by type

order by type;

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

1

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

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',
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206844%2fsegregate-column-to-muti-columns-based-on-datetime%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

I think you want conditional aggregation:

select type, 

       max(case when tran_dt = '01/01/2018 12:00:00' then conent end) as c_20180101_12,

       max(case when tran_dt = '01/01/2018 01:00:00' then conent end) as c_20180101_01

from t

group by type

order by type;

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

1

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

add a comment |

up vote
1
down vote

I think you want conditional aggregation:

select type, 

       max(case when tran_dt = '01/01/2018 12:00:00' then conent end) as c_20180101_12,

       max(case when tran_dt = '01/01/2018 01:00:00' then conent end) as c_20180101_01

from t

group by type

order by type;

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

1

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

add a comment |

up vote
1
down vote

I think you want conditional aggregation:

select type, 

       max(case when tran_dt = '01/01/2018 12:00:00' then conent end) as c_20180101_12,

       max(case when tran_dt = '01/01/2018 01:00:00' then conent end) as c_20180101_01

from t

group by type

order by type;

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

I think you want conditional aggregation:

select type, 

       max(case when tran_dt = '01/01/2018 12:00:00' then conent end) as c_20180101_12,

       max(case when tran_dt = '01/01/2018 01:00:00' then conent end) as c_20180101_01

from t

group by type

order by type;

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

edited Nov 8 at 13:31

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

answered Nov 8 at 11:39

Gordon Linoff

743k32285390

1

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

add a comment |

1

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

GROUP BY type and no aggregation around the CASE expression?
– MatBailie
Nov 8 at 11:56

@MatBailie . . . Thank you.
– Gordon Linoff
Nov 8 at 13:31

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xtykutl