Convert Row data into column against one id

Akhter HUssain

7y808Learn iOS Programming

I have data in Rows like

DateTime Status EmpId

2018-05-28 8:00 01 2000347

2018-05-28 20:18 02 2000347

2018-05-28 8:00 01 2000348

2018-05-28 17:18 02 2000348

Now i want above data convert into columns like this

EmpID Status (1)INTime Status(2)OutTime

2000347 2018-05-28 8:00 2018-05-28 20:18

2000348 2018-05-28 8:00 2018-05-28 17:18

means same date data will come against one ID

Kindly help ..

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted answer
It will be good to post a separate question for that and agian requesting you to accept the answer if it was right.

By the way you can create a stored procedure of the below sql statements and get data from that procedure to display into the Asp.net gridview.

Follow the link below link of articles for help

https://www.c-sharpcorner.com/uploadfile/puranindia/stored-procedures/

https://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx
https://www.c-sharpcorner.com/forums/display-data-in-gridview-using-storedprocdure
https://www.c-sharpcorner.com/article/crud-using-stored-procedure-in-asp-net-gridview-real-time/

Hope this will help you.
Jignesh Kumar7y ago
Hi Akhter,

You can create Store procedure As Suaj Kumar suggested and you can call same procedure from your Code.
Create Procedure usp_GetEmployeeLogInLogOutStatus
As
Begin
Select
   EmpId,
   Min(Case When LoginStatus = '01' Then LogINAndOut End) As [Status(1)INTime],
   Max(Case When LoginStatus = '02' Then LogINAndOut End) As [Status(2)OutTime]
From MyStatus
Group By EmpId
End
Please refer below link to call procedure in C#.
https://www.c-sharpcorner.com/uploadfile/mahesh/executing-a-stored-procedure-programmatically/
Akhter HUssain7y ago
thanks for helping but how i will use this in asp.net webform c# ,just u guide me sql
Suraj Kumar7y ago
Another solutions is as shown below
Select
   sEmpId,
   Max(Case When sStatus = '01' Then dDateTime End) As [In],
   Max(Case When sStatus = '02' Then dDateTime End) As [Out]
From #temp
Group By sEmpId
Suraj Kumar7y ago
You can find the modified solution below. Normally identifier cannot start with digit. You need to wrap columns with []
create table #temp (dDateTime Datetime, sStatus varchar(20), sEmpId varchar(20))
insert into #temp values ('2018-05-28 8:00', '01', '2000347'),
                         ('2018-05-28 20:18', '02', '2000347'),
                         ('2018-05-28 8:00', '01', '2000348'),
                         ('2018-05-28 17:18', '02', '2000348')
--lect * from #temp

select sEmpId, [01] as 'Status (1)INTime', [02] as 'Status(2)OutTime'
from
(
  select sEmpId,dDateTime,sStatus
  from #temp
) d
pivot
(
  max(dDateTime)
  for sStatus in ([01], [02])
) piv;

drop table #temp
Alternative you can try the below query if both in and out date will be same date
select sEmpId,
       min(dDateTime) InTime,
       max(dDateTime) OutTime
from #temp
group by sEmpId, day(dDateTime)
Suraj Kumar7y ago
You can try the following SQL code
create table #temp (dDateTime Datetime, sStatus varchar(20), sEmpId varchar(20))
insert into #temp values ('2018-05-28 8:00', 's1', '2000347'),
                         ('2018-05-28 20:18', 's2', '2000347'),
                         ('2018-05-28 8:00', 's1', '2000348'),
                         ('2018-05-28 17:18', 's2', '2000348')
--lect * from #temp

select sEmpId, s1 as 'Status (1)INTime', s2 as 'Status(2)OutTime'
from
(
  select sEmpId,dDateTime,sStatus
  from #temp
) d
pivot
(
  max(dDateTime)
  for sStatus in (s1,s2)
) piv;

drop table #temp
Here you can note I have inserted s1, s2 in place of 01, 02 in table. It is because the column name can not be in started with numbers and in Pivot we need column name.

The output is as shown below -
sEmpId  Status (1)INTime Status(2)OutTime
2000347 2018-05-28 08:00:00.000 2018-05-28 20:18:00.000
2000348 2018-05-28 08:00:00.000 2018-05-28 17:18:00.000
You can find the live demo here - https://rextester.com/EIIX69146