Click here to Skip to main content
14,384,144 members
Rate this:
Please Sign up or sign in to vote.
I have a table it contains value like
create table tableName (
dattime datetime, id int)

insert into tableName (dattime,id) values ('2019-05-07 07:15:000',1)
insert into tableName (dattime,id) values ('2019-05-07 13:50:000',1)
insert into tableName (dattime,id) values ('2019-05-07 07:15:000',2)
insert into tableName (dattime,id) values ('2019-05-07 13:50:000',2)

dattime                 | id
2019-05-07 07:15:00.000	| 1
2019-05-07 13:50:00.000	| 1
2019-05-07 07:15:00.000	| 2
2019-05-07 13:50:00.000	| 2 



My Question is how to make this value as

id  |   col1              | col2
1   |   2019-05-07 07:15  | 2019-05-07 13:50
2   |   2019-05-07 07:15  | 2019-05-07 13:50


Please help!.....

What I have tried:

SELECT id, dattime FROM   
(SELECT id, dattime  FROM tableName )col1
PIVOT  (  
max(id) FOR dattime IN (dattime)) AS col2
Posted
Updated 7-May-19 0:48am
v2
Comments
Jörgen Andersson 7-May-19 5:30am
   
There is nothing identifying what should become Col1 and Col2.
And how would you handle the case where there are three rows for an ID?
Rate this:
Please Sign up or sign in to vote.

Solution 3

The simplest way is just to use GROUP BY:
SELECT ID, MIN(Dattime) AS Col1, MAX(Dattime) As Col2
FROM tableName 
GROUP BY ID
   
Comments
nityanand NCR 7-May-19 6:03am
   
5
Aravindba 7-May-19 6:17am
   
5+,
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hi
I think u need to get data in code like u mention above may be for report or chart , better declare data-table and use query to retrieve one by one and bind value dynamically in that data-table by programming code.

use this query to take id only
select id  from tablename group by id
, then loop with result,
e:g id '1 and 2 u will get 2 result each,
so first value bind in col 1 and 2 value bind in col 2 , same like for id - 2
first value bind in col 1 and 2 value bind in col 2

Regards,
Aravind
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100