How to divide the single column of a database table in two different columns? with same ID

Question

1.00/5 (1 vote)

See more:

I have one column named as Time with another column named as ID.
I want to divide the column Time in two different columns as InTime & OutTime.
But the important thing is that if the count of that ID present in a ID column is even, the corresponding value present at that ID of column Time will be inserted in a column named as OutTime & if the count of that ID is odd the corresponding value present at that ID of column Time will be inserted in a column named as InTime.

ie if values are like this

SQL

Insert #temp values(1,'09:30') 
Insert #temp values(1,'12:30') 
Insert #temp values(1,'18:30')

I want data as

ID      Intime         Outtime
1       09:30          12:30
1       Null           18:30

Its Urgent..
Please help me as I'm new in this field.....
THANK YOU in Advance.

Posted 13-May-13 7:12am

Makp

Updated 13-May-13 8:52am

Maciej Los

v2

Add a Solution

Comments

R. Giskard Reventlov 13-May-13 13:35pm

Why is it urgent? It also doesn't seem to make much sense. Is this homework?

Maciej Los 13-May-13 14:56pm

OK, you want to get some output data... But on which criteria? I don't see any dependencies...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

db7uk · Answer 1 · 2013-05-13T09:37:00

Solution 1

You need to either understand PIVOT / UNPIVOT

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

or

Create two select statements and use them as derived tables. Then you can join on ID and get your output.

i.e.

SQL

Select a.Id, a.Time InTime, b.Time OutTime
from
(select id, time from #Temp where ......) a
inner join
(select id, time from #Temp where ......) b
on a.Id = b.Id

Posted 13-May-13 9:37am

db7uk

Comments

Maciej Los 13-May-13 15:56pm

Pivot? In which place do you recommend to use it?

db7uk 13-May-13 16:56pm

Do you require an example? The OP did not really provide enough information to go on. Basically you could (if the query permits) use the PIVOT function to convert rows to columns. One could use the Max / avg aggregate function needed with pivot to do just that. I am not 100% this would work on the OP original question based on information but could point in the right direction?

Maciej Los 14-May-13 1:49am

Have you seen my comment to the question? Have you seen example data and expected output? We don't know the rules / criteria on which OP wants to divide columns. "...OP did not really provide enough information..." - This is it what i pointing!