Transforming a table - logic question

Question

Post reply

Transforming a table - logic question

Paul Cressey

Grasshopper

Points: 22
More actions
November 13, 2006 at 10:22 am

#91937

I have a table in our SQL database that tracks the sales of products by year and by month. The table schema is as follows:
Item_ID
Year_ID
Quantity01
Quantity02
.
.
.
Quantity12
Obviously Quantity01 would be quantity sold for January, Quantity02 would be quantity sold for February, and so on.
I need to be able graph/output the quantity sold for the past 12 months. This requires creating a temp table somehow so the data can be read properly. I was thinking of breaking it down by Item, Year, Month, Quantity...
Item_ID
Year_ID
Month_ID
Quantity
I am just stuck on how I could go about achieving this with a Stored Procedure, and what the actual logic to do this would be. If anyone has ideas or better solutions I would appreciate it!
// Paul

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18874 More actions · Answer 1

If possible, normalize the schema although a reporting tool should be able to cope with the current table structure.

To get the output requested, something like the following should work:

SELECT

T.Item_ID

,T.Year_ID

,N.Nbr AS Month_ID

,CASE N.Nbr

WHEN 1 THEN Quantity01

WHEN 2 THEN Quantity02

WHEN 3 THEN Quantity03

WHEN 4 THEN Quantity04

WHEN 5 THEN Quantity05

WHEN 6 THEN Quantity06

WHEN 7 THEN Quantity07

WHEN 8 THEN Quantity08

WHEN 9 THEN Quantity09

WHEN 10 THEN Quantity10

WHEN 11 THEN Quantity11

WHEN 12 THEN Quantity12

END AS Quantity

FROM

YourTable T

CROSS JOIN (

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12) N (Nbr)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I just don't see how this can solve the problem. Care to post some sample data, the reuiqred output and wether this code works for you??

DaveItz SSCrazy Points: 2146 More actions · Answer 3

At a casual glance, it seems like he is telling it to create 12 rows for every one row in the original table. The CASE statement would be telling it which row you are on and therefore which field to use for the particular row.

(Odd syntax at the end, though. Didn't know you can add the fieldnames adhoc to a subquery like that.)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ya you get 12 rows instead of one... but you still have 12 columns instead of 12!??!

I don't see how that solved the problem... I'm not even sure of how he wants the problem to be solved.

DaveItz SSCrazy Points: 2146 More actions · Answer 5

You wouldn't get 12 columns.

Only 4 are selected:

   Item_ID
   Year_ID
   Month_ID
   Quantity

I'm not sure what the new Item_ID is supposed to represent now.

(Nevermind, it probably means what it says)

The 12 quantity fields represented the quantities of each month in the year. Now, once you create the new table structure and insert the results from the query, each month is on its own row. Apparently he is convinced that the new structure will help him write a query for a graph or chart or something.

Paul Cressey Grasshopper Points: 22 More actions · Answer 6

You get 12 rows per item (per year), with one column for the corresponding quantity sold for that item/year (not 12 columns).

Example:

01001 | 2005 | 1 | 12345

01001 | 2005 | 2 | 8581

01001 | 2005 | 3 | 128458

01001 | 2005 | 4 | 85481

.

I will let you know if I can achieve what I wanted with this syntax, but that query does exactly what I asked for! :o)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I see, said the blind man who should have started drinking coffee this morning...