Moving data in a column to a row via scipt

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Moving data in a column to a row via scipt

Author

Topic

bmatthews
Starting Member

8 Posts

Posted - 2010-01-04 : 16:40:52

Hello,

I am working on a small music library for myself via SQL. I imported the music library via Excel and have a normalized table like so.

tbl_music
---------
genre1, album
genre1, album
genre2, album
etc,

and I want to make a script that takes the data and puts it into this format

tbl_revised_music
-----------
genre1, album1, album2, album3
genre2, album1, album2
etc,

note that genres can have a different number of albums. Thanks for any pointers!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-04 : 17:06:45

You need two tables.

genre
id int
genre varchar(255)

album
id int
genre_id int
album varchar(255)

please, please, please do nothing like this:
tbl_revised_music
-----------
genre1, album1, album2, album3
genre2, album1, album2

No, you're never too old to Yak'n'Roll if you're too young to die.