Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Niki
Yak Posting Veteran
51 Posts |
Posted - 2013-03-05 : 18:57:14
|
I have table A with StuID and table B with Stuid & multiple Enter & leavedates. I want to create multiple rows for each enter and leavedate.Table A StuID 99999Table B Stuid 99999 edate ldate edate1 ldate1 edate2 ldate2Result should look like99999 edate ldate99999 edate1 ldate199999 edate2 ldate2How can I do that? I tried CROSS APPLY but I don't think I am using it correctly. Please help.Niki |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2013-03-05 : 19:04:00
|
They are Datetime type. This is how I am doing it and I know there must be a better way to do ot. Please helpselect a.studentno, b.schno , edate as Enterdate, ldate as LeaveDatefromtblA ainner join tblB b on a.studentno = b.Studentnounion allselect a.studentno, schno2 , edate2 as Enterdate, ldate2 as LeaveDatefromtblA ainner join tblB b on a.studentno = b.Studentnounion allselect a.studentno, schno3 , edate3 as Enterdate, ldate3 as LeaveDatefromtblA ainner join tblB b on a.studentno = b.Studentno |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 01:16:34
|
[code]select p.studid,p.val as edateval, q.val as ldatevalfrom(select studid,dates,val,row_number() over (partition by studid order by dates) as seqfrom (select studid,edate,edate1,edate2 from table )tunpivot (val for dates in (edate,edate1,edate2))u)pinner join (select studid,dates,val,row_number() over (partition by studid order by dates) as seqfrom (select studid,ldate,ldate1,ldate2 from table )munpivot (val for dates in (ldate,ldate1,ldate2))n)qon q.studid=p.studidand q.seq=p.seq[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|