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 |
|
gvmk27
Starting Member
44 Posts |
 Posted - 2015-02-12 : 14:48:15
|
| Hi I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as belowI need results for each parent like thisParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender] |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-12 : 15:24:39
|
| [code]select p.ParentID, LastName, FirstName , max(case k when 1 then kidname end) as Kid1Name , max(case k when 2 then kidname end) as Kid2Name , max(case k when 3 then kidname end) as Kid3Name , max(case k when 1 then Age end) as Kid1Age , max(case k when 2 then Age end) as Kid2Age , max(case k when 3 then Age end) as Kid3Age , ... etc.from parent pjoin (select * , k = ROW_NUMBER() over(partition by parentid order by age) from kids) kon p.Parentid = k.Parentidgroup by p.ParentID, LastName, FirstName[/code] |
 |
|
|
|
|
|
|
|
(multiple) rows into a (multiple) columns