Transpose large table

[YonKee]

Does anybody know how to switch rows into columns

i can't transpose in excel cause it is a 500.000 records table.

the first three coulumns are id's and the next 25 culumns are each labled a different month,containing values.

i want to create three id.coulmns,a time id columns and a column containing the values.

Thanks

Yon Kee

 

[fluteplr]

A union should do it.

select id, 'Month 1', month1 from mytable
union all
select id, 'Month 2', month2 from mytable
...
union all
select id, 'Month 25', month25 from mytable

Obviously replace month1 etc. with the actual field names you may also want to replace the character constants with a datetime depending on your needs.

BTW if it were up to me I would do the transformation once and get rid of the original table. The transformed format is a better way to store this data.

 

[cybercop23]

fluteplr, does this also apply if i have an item master file with item movement? Ok, ok.... what I mean is this:

I have 100k items that might sell amongs the 1000 stores.
I need to keep a rolling 104 weeks of selling info.

Should my table be:
Store, item, week1, week1, week3.... (sale is under weekX)
OR
Date, Store, item, sale

In the first case once I create the table it will not grow, except for new items. However, in the second case I'll just be appending to it weekly.

This kinda goes to DB 101 class and normalization. I'm just concerned about the size and performance. The first design yelds a 1 billion record table, static in size and the second one, well.... dono.

Any suggestions?

 

[fluteplr]

If it were me I would do the second.

Date, store, item sale, it gives you a lot more flexibility. You can always delete items that are older that the 104 weeks you need to store.

 

[SQLSister]

fluteplr is right, absolutely go with date, store, item, sale. It is easier to maintain (what if the requirement suddenly becomes 156 weeks of data?), easier to write queries against, and easier to know exactly which dates are covered by the data.