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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 data row into data column per staffno

Author  Topic 

nurazura
Starting Member

3 Posts

Posted - 2013-01-09 : 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

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)s
GROUP BY staffno
[/code]


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/

Go to Top of Page

nurazura
Starting Member

3 Posts

Posted - 2013-01-16 : 04:33:00
I git it..

Thank you very much...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 22:34:02
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -