SQL 2000 Dynamic Columns

[nsalinas]

Hello All,
I am working in SQL Server 2000 and I am trying to convert a table from this:

CaseName1 Child1
CaseName1 Child2
CaseName1 Child3

CaseName2 Child1
CaseName2 Child2
CaseName2 Child3
CaseName2 Child4

CaseName3 Child1
CaseName3 Child2

CaseName4 Child1
CaseName4 Child2
CaseName4 Child3
CaseName4 Child4
CaseName4 Child5


To this (where the number of columns is dynamic):

CaseName1 Child1 Child2 Child3
CaseName2 Child1 Child2 Child3 Child4
CaseName3 Child1 Child2
CaseName4 Child1 Child2 Child3 Child4 Child5

Can this be done?!
Any help or suggestions would be greatly appreciated,
Thank you!

 

[stevexff]

Don't want to be a wet blanket, but the whole reason third normal form was invented was precisely to stop people doing things like this. In order to define a table, you need to know how many columns you are ever likely to need. Then make most of them nullable. Then have a massive SELECT, and loop through all the returned columns checking for nulls.

Even assuming you could create a table that had a different number of columns for each row, how would you write the code to retrieve the rows? (Note: almost every text ever written on programmatic SQL says that using SELECT * in your code is usually a Really Bad Idea)

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]