Bad Table Design

[spiff2002]

We have a Sales History table that is on our DB for reporting purposes only. The problem is that it has a really bad design and I am tryin to fix a it a little for better data manipulation. The table desing is as follows
table sales
customer# Item# jan04 feb04 ..... jan03....jan02 ...dec01
1 1 4 6 10 0 0
1 2 5 6 2 4 4
2 1 3 5 10 1 3

The table has 48 columns across one for each month on the past 4 years.


I am wondering if there is a query even using temp tables that would help me do this:
customer# Item# period sales
1 1 2004-01 4
1 1 2004-02 6
1 1 2003-01 10
1 1 2002-01 0
1 1 2001-12 0
1 2 2004-01 5
1 2 2004-02 6
1 2 2003-01 2
1 2 2002-01 4
1 2 2001-12 4
2 1 2004-01 3
2 1 2004-02 5
2 1 2003-01 10
2 1 2002-01 1
2 1 2001-12 3

Thank you for your ideas
S.

 

[SQLSister]

Try a union query:

Select customerNo, ITemNo, '2001-12' as Period, sum (Dec01) from Sales group by CustomerNo, ItemNo Having Dec01 >0
Union
Select customerNo, ITemNo, '2002-1' as Period, sum (Jan02) from Sales group by CustomerNo, ItemNo Having Jan02 >0

etc for each month you want.

Better of course to make a related table and fix the structure.


Questions about posting. See faq183-874