n rows to n/2 columns : Sqlserver
I am having the table like this
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 09.10)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.40)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 17.30)
I want in this format
OutPut:
StaffNo ADate InTime OutTime
12333, 12-02-2013 09.10 10.03
12333, 12-02-2013 10.40 17.30
sql-server sql-server-2008 edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
add a comment |
I am having the table like this
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 09.10)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.40)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 17.30)
I want in this format
OutPut:
StaffNo ADate InTime OutTime
12333, 12-02-2013 09.10 10.03
12333, 12-02-2013 10.40 17.30
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
2
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05
add a comment |
I am having the table like this
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 09.10)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.40)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 17.30)
I want in this format
OutPut:
StaffNo ADate InTime OutTime
12333, 12-02-2013 09.10 10.03
12333, 12-02-2013 10.40 17.30
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
I am having the table like this
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 09.10)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.40)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 17.30)
I want in this format
OutPut:
StaffNo ADate InTime OutTime
12333, 12-02-2013 09.10 10.03
12333, 12-02-2013 10.40 17.30
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
edited Nov 16 '18 at 4:32
Cœur
19.1k9114155
19.1k9114155
asked Feb 21 '13 at 14:01
NirmalNirmal
1
asked Feb 21 '13 at 14:01
NirmalNirmal
1
asked Feb 21 '13 at 14:01
NirmalNirmal
1
1
2
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05
add a comment |
2
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05
2
2
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05
add a comment |
1 Answer
1
active
oldest
votes
Something like this will work for your sample rows:
with attnJoin as
(
select attn.StaffNo
, attn.ADate
, InTime = attn.ATime
, OutTime = prev.ATime
, eventNumber = row_number() over (partition by attn.StaffNo, attn.ADate order by attn.ATime)
from attn
outer apply
(
select top 1 ATime
from attn prev
where attn.StaffNo = prev.StaffNo
and attn.ADate = prev.ADate
and attn.Atime < prev.ATime
order by ATime
) prev
)
select StaffNo
, ADate
, InTime
, OutTime
from attnJoin
where eventNumber % 2 = 1
SQL Fiddle with demo.
However this might be flaky in certain circumstances, e.g. missing data, multiple days, etc. Only you can find out for sure when looking at your live data.
Also, as has been noted, there might be more fundamental issues with the design, such as storing time separately from the date when there's no need. It would also be easier if you had a flag indicating which row was in/out.
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
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%2f15004166%2fn-rows-to-n-2-columns-sqlserver%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something like this will work for your sample rows:
with attnJoin as
(
select attn.StaffNo
, attn.ADate
, InTime = attn.ATime
, OutTime = prev.ATime
, eventNumber = row_number() over (partition by attn.StaffNo, attn.ADate order by attn.ATime)
from attn
outer apply
(
select top 1 ATime
from attn prev
where attn.StaffNo = prev.StaffNo
and attn.ADate = prev.ADate
and attn.Atime < prev.ATime
order by ATime
) prev
)
select StaffNo
, ADate
, InTime
, OutTime
from attnJoin
where eventNumber % 2 = 1
SQL Fiddle with demo.
However this might be flaky in certain circumstances, e.g. missing data, multiple days, etc. Only you can find out for sure when looking at your live data.
Also, as has been noted, there might be more fundamental issues with the design, such as storing time separately from the date when there's no need. It would also be easier if you had a flag indicating which row was in/out.
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
add a comment |
Something like this will work for your sample rows:
with attnJoin as
(
select attn.StaffNo
, attn.ADate
, InTime = attn.ATime
, OutTime = prev.ATime
, eventNumber = row_number() over (partition by attn.StaffNo, attn.ADate order by attn.ATime)
from attn
outer apply
(
select top 1 ATime
from attn prev
where attn.StaffNo = prev.StaffNo
and attn.ADate = prev.ADate
and attn.Atime < prev.ATime
order by ATime
) prev
)
select StaffNo
, ADate
, InTime
, OutTime
from attnJoin
where eventNumber % 2 = 1
SQL Fiddle with demo.
However this might be flaky in certain circumstances, e.g. missing data, multiple days, etc. Only you can find out for sure when looking at your live data.
Also, as has been noted, there might be more fundamental issues with the design, such as storing time separately from the date when there's no need. It would also be easier if you had a flag indicating which row was in/out.
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
add a comment |
Something like this will work for your sample rows:
with attnJoin as
(
select attn.StaffNo
, attn.ADate
, InTime = attn.ATime
, OutTime = prev.ATime
, eventNumber = row_number() over (partition by attn.StaffNo, attn.ADate order by attn.ATime)
from attn
outer apply
(
select top 1 ATime
from attn prev
where attn.StaffNo = prev.StaffNo
and attn.ADate = prev.ADate
and attn.Atime < prev.ATime
order by ATime
) prev
)
select StaffNo
, ADate
, InTime
, OutTime
from attnJoin
where eventNumber % 2 = 1
SQL Fiddle with demo.
However this might be flaky in certain circumstances, e.g. missing data, multiple days, etc. Only you can find out for sure when looking at your live data.
Also, as has been noted, there might be more fundamental issues with the design, such as storing time separately from the date when there's no need. It would also be easier if you had a flag indicating which row was in/out.
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
Something like this will work for your sample rows:
with attnJoin as
(
select attn.StaffNo
, attn.ADate
, InTime = attn.ATime
, OutTime = prev.ATime
, eventNumber = row_number() over (partition by attn.StaffNo, attn.ADate order by attn.ATime)
from attn
outer apply
(
select top 1 ATime
from attn prev
where attn.StaffNo = prev.StaffNo
and attn.ADate = prev.ADate
and attn.Atime < prev.ATime
order by ATime
) prev
)
select StaffNo
, ADate
, InTime
, OutTime
from attnJoin
where eventNumber % 2 = 1
SQL Fiddle with demo.
However this might be flaky in certain circumstances, e.g. missing data, multiple days, etc. Only you can find out for sure when looking at your live data.
Also, as has been noted, there might be more fundamental issues with the design, such as storing time separately from the date when there's no need. It would also be easier if you had a flag indicating which row was in/out.
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
answered Feb 21 '13 at 14:37
Ian PrestonIan Preston
32k77479
32k77479
add a comment |
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%2f15004166%2fn-rows-to-n-2-columns-sqlserver%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
2
Looks like you're using string/text/varchar columns for temporal data. That's a bad idea.
– Joel Coehoorn
Feb 21 '13 at 14:02
Does it means formatting output..?
– SenthilPrabhu
Feb 21 '13 at 14:05