Click here to Skip to main content
11,642,381 members (68,027 online)
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
Web02 | 2.8.150731.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2015
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