Foreach Column In Row, Create New Row

[mcfly1204]

I have a recipe table that consists of a recipe id as well as seven column, each of the seven columns consisting of an ingredient. I would like to create a separate row for each column in the row. For example, i currently have:

recipeid,ind1,i nd2,ind3,ind4,i nd5,ind6,ind7

I would like to have:

recipeid,ind1
recipeid,ind2
recipeid,ind3
...

How would I go about this?

[ck9663]

Try to UNPIVOT your table.

Happy Coding!!!

~~ CK

[mcfly1204]

Try to UNPIVOT your table.

Happy Coding!!!

~~ CK

I am using SQL Server 2000, so the pivot/unpivot commands are not available. I am looking into how to accomplish this with union all commands.

[ck9663]

Try doing a cross join.

Happy Coding!!!

~~ CK