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)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 446
1 Sergey Alexandrovich Kryukov 360
2 Shai Vashdi 318
3 OriginalGriff 265
4 Peter Leow 171
0 Sergey Alexandrovich Kryukov 9,109
1 OriginalGriff 5,290
2 Peter Leow 4,010
3 Maciej Los 3,535
4 Abhinav S 3,263


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