Multiple Rows into one by adding new columns

Help...this is driving me nuts.

I have a table with the following columns:

Name|Address|Child|ChildAge

Bill - 1 ParkPlace - Ben - 2

Bill - 1 ParkPlace - Jen - 4

Jill - 2 ParkPlace - Kev - 6

Jill - 2 ParkPlace - Bev - 7

Jill - 2 ParkPlace - Jeb - 7

There can be multiple Children/Age per name so there are multiple rows. I would like to create a table from this that put all children w/age on one row by adding as many Child and Age columns as needed:

Name|Address|Child|ChildAge|Child|ChildAge|Child|ChildAge...

Bill - 1 ParkPlace - Ben - 2 - Jen - 4

Jill - 2 ParkPlace - Kev - 6 - Bev - 7 - Jeb - 7

Thanks for any help.

Bill:

What you are asking to do is generally considered a bad design. What is motivating you to want to do this? I think it would be better to address the root problem or set of problems than to jump right in to all of these columns. Also, which version of SQL Server are you using?

Kent

|||

Thanks Kent.

Sorry, I should've been a little clearer. My goal is to create a report (spreadsheet) from the existing table as described above. I don't necessarily need to create a new table. I just thought that it would be best to address the layout before dumping it into excel.

I am using 2005 with Integration Services.

|||

Code Snippet

createfunction dbo.ListChildren ( @.Name as varchar(25), @.Address as varchar(25))

returns varchar(8000)

as

begin

declare @.list varchar(8000)

select @.list =coalesce( @.list +' - ','')+ Child +'-'+cast(ChildAge as varchar(3))

from tbl where Name = @.Name AND Address = @.Address

return @.list

end

GO

createtable dbo.tbl (Name varchar(25), Address varchar(25),

Child varchar(25), ChildAge int)

insertinto dbo.tbl values('Bill','1 ParkPlace','Ben', 2)

insertinto dbo.tbl values('Bill','1 ParkPlace','Jen', 4)

insertinto dbo.tbl values('Jill','2 ParkPlace','Kev', 6)

insertinto dbo.tbl values('Jill','2 ParkPlace','Bev', 7)

insertinto dbo.tbl values('Jill','2 ParkPlace','Jeb', 7)

select Name, Address, dbo.ListChildren(Name, Address)as Children

from dbo.tbl

groupby Name, Address

|||

This suggestion may provide you the output you desire.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20),
Address varchar(25),
ChildName varchar(20),
ChildAge int
)

INSERT INTO @.MyTable VALUES( 'Bill', '1 ParkPlace', 'Ben', 2 )
INSERT INTO @.MyTable VALUES( 'Bill', '1 ParkPlace', 'Jen', 4 )
INSERT INTO @.MyTable VALUES( 'Jill', '2 ParkPlace', 'Kev', 6 )
INSERT INTO @.MyTable VALUES( 'Jill', '2 ParkPlace', 'Bev', 7 )
INSERT INTO @.MyTable VALUES( 'Jill', '2 ParkPlace', 'Jeb', 7 )

SELECT DISTINCT
Name,
Address,
Children = substring( ( SELECT ', ' + ( ChildName + ' -' + cast( ChildAge AS varchar(2))) AS [text()]
FROM @.MyTable t2
WHERE t2.Name = t1.Name
ORDER BY Name
FOR XML path(''), elements
), 3, 1000
)
FROM @.MyTable t1

Name Address Children
-- -
Bill 1 ParkPlace Ben -2, Jen -4
Jill 2 ParkPlace Kev -6, Bev -7, Jeb -7