Convert unique row to columns

[lavadan]

I have a SQL table

Parent ID Children
1 Smith
1 Jenny
1 Ryan
2 Joe
2 Jane

I want the result to be

ParentID Children
1 Smith, Jenny, Ryan
2 Joe, Jane

How can achieve this result?

 

[imex]

Try something like this:

SELECT DISTINCT
    a.ParentID, 
    STUFF( (SELECT ', ' + s.Children
            FROM MyTable as s
            WHERE s.ParentID = a.ParentID
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
           ,1, 2, '') as Children
FROM MyTable as a

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[SkipVought]

...and then there's the Duggars! ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lavadan]

How would I modify the query to get the childrens name in seperate column


I have a SQL table

Parent ID Children
1 Smith
1 Jenny
1 Ryan
2 Joe


I want the result to be. Only top 2 children will be shown. If a parent id has 10 children, then I want only the top 2 in seperate column

ParentID Children1 Children2
1 Smith Jenny
2 Joe Null

How can achieve this result?

 

[gmmastros]

How do you decide which 2 children get displayed?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

WITH orderlyChildren AS (
SELECT ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY ChildName) ChildNumber, *
  FROM yourTable
)

SELECT ParentId,
       MAX(CASE ChildNumber WHEN 1 THEN ChildName END) Child1,
       MAX(CASE ChildNumber WHEN 2 THEN ChildName END) Child2
  FROM orderlyChildren
 GROUP BY ParentId