Author |
Topic |
nurazura
Starting Member
3 Posts |
Posted - 2013-01-09 : 22:38:32
|
I have table - staffchild staffno, childname, childDOB, ChildbirthcertnoA001, AARON HANYE, 25/1/2000, A12G44555A001, ERINN HANYE, 2/15/2005, A12GX4886A001, MARINA HANYE, 3/9/2010, A12GZ7851A002, BILL WANG, 14/5/1999, A12G44555A002, SERINA WANG, 17/7/2008, A12G44555......i want to change the data row to data column. In this case, 1 staff 1 row data of all children.Staffno, childname1, childDOB1, Childbirthcertno1, childname2, childDOB2, Childbirthcertno2, childname3, .... etcA001, AARON HANYE, 25/1/2000, A12G44555, ERINN HANYE, 2/15/2005, A12GX4886, MARINA HANYE, 3/9/2010, A12GZ7851A002, BILL WANG, 14/5/1999, A12G44555, A002, SERINA WANG, 17/7/2008, A12G44555Please help me. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:47:33
|
[code]SELECT staffno,MAX(CASE WHEN Seq=1 THEN childname END) as childname1,MAX(CASE WHEN Seq=1 THEN childDOB END) as childDOB1,MAX(CASE WHEN Seq=1 THEN childbirthcertno END) as childbirthcertno1,MAX(CASE WHEN Seq=2 THEN childname END) as childname2,MAX(CASE WHEN Seq=2 THEN childDOB END) as childDOB2,MAX(CASE WHEN Seq=2 THEN childbirthcertno END) as childbirthcertno2,...FROM (SELECT ROW_NUMBER() OVER (PARTITION BY staffno ORDER BY childDOB) AS Seq,* FROM staffchild)sGROUP BY staffno[/code]if you want to make it dynamic usehttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nurazura
Starting Member
3 Posts |
Posted - 2013-01-16 : 04:33:00
|
I git it..Thank you very much...! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-16 : 22:34:02
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|