Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
i have two table.1)userinfo
fields:
userid (primarykey)
name

2)checkinout
fields:
userid
checktime
checktype
 
data in userinfo
 
userid   name
--------------
1         abc
 
data in checkinout
 
userid   checktime   checktype
----------------------------------
1   1/1/2013 10am   0
1   1/1/2013 5pm    1
1   2/1/2013 10am   0
1   2/1/2013 5pm    1
 
o is indicate intime,1 indicate outtime
 
now i have to display data in gridview like this way...
 
userid   name    Intime         outtime            intype   outtype
-------------------------------------------------------------------
1        abc   1/1/2013 10am   1/1/2013 5pm   0        1
 
one condition is that ,i have to not change the table field.
 
how to do this?
 

 

 

Posted 28-Jan-13 6:58am
Edited 28-Jan-13 7:13am
v5
Comments
Member 9511889 at 29-Jan-13 0:58am
   
ALTER PROCEDURE dbo.Biometrics_Select_Data
@Userid numeric(18,0)
 
AS
 
--- select Checkinout.Userid,Checkinout.CheckTime,Checkinout.CheckType,
--- Checkinout.Sensorid,Checkinout.Logid,Checkinout.Checked,Checkinout.WorkType,
--- Checkinout.AttFlag,Userinfo.[Name] from Checkinout left join Userinfo
--- On Checkinout.Userid=Userinfo.Userid where Userid=@Userid
 

SELECT Checkinout.*,Userinfo.Name
FROM Checkinout LEFT JOIN Userinfo
pivot(CheckType IN(0)) as Intime --, pivot(checktype in (1)) as outtime
ON (Checkinout.Userid = Userinfo.Userid)
where (Checkinout.Userid = @Userid)


 

 

RETURN
 

i have try this ,but it gives error like...incorect syntex near in

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is called a "pivot". Read up on a good example here[^].
  Permalink  

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



Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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