| Author |
 Topic  |
|
|
nurazura
Starting Member
3 Posts |
 Posted - 01/09/2013 : 22:38:32
|
I have table - staffchild
staffno, childname, childDOB, Childbirthcertno
A001, AARON HANYE, 25/1/2000, A12G44555
A001, ERINN HANYE, 2/15/2005, A12GX4886
A001, MARINA HANYE, 3/9/2010, A12GZ7851
A002, BILL WANG, 14/5/1999, A12G44555
A002, 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, .... etc
A001, AARON HANYE, 25/1/2000, A12G44555, ERINN HANYE, 2/15/2005, A12GX4886, MARINA HANYE, 3/9/2010, A12GZ7851
A002, BILL WANG, 14/5/1999, A12G44555, A002, SERINA WANG, 17/7/2008, A12G44555
Please help me. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/09/2013 : 22:47:33
|
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)s
GROUP BY staffno
if you want to make it dynamic use
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
nurazura
Starting Member
3 Posts |
Posted - 01/16/2013 : 04:33:00
|
I git it..
Thank you very much...! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/16/2013 : 22:34:02
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
data row into data column per staffno
