Ranking 10 Columns down to 2. 1

[woogoo]

Mmmm, maybe not the best title for the problem but here is what I've got :

SELECT Item, I1, V1, I2, V2, I3, V3, I4, V4, I5, V5 FROM MyTable ;

Simple, however I now want to return only One set of Items (I) and one set of Prices (P) from the above result set ordered on P ASC.


ROW 1 Item A, 0, 1.5, 1, 1.7, 2, 1.9, 3, 2.1, 4, 2.9, 5, 4.3
ROW 2 Item B, 1, 1.6, 1, 1,7, 2, 2.0, 3, 2.1, 4, 2.5, 7, 3.8

For example:

ROW 1, Item A, 0, 1.5
ROW 2, Item B, 1, 1.6
ROW 3, Item B, 1, 1.7
ROW 4, Item A, 1, 1.7
ROW 5, Item A, 2, 1.9
ROW 6, Item B, 2, 2.0
ROW 7, Item A, 3, 2.1
ROW 8, Item B, 3, 2.1
ROW 7, Item B, 4, 2.5
ROW 8, Item A, 4, 2.9
...

Anyone got any ideas?



woogoo

 

[gmmastros]

SELECT Item, I1, V1
FROM MyTable 

Union All
Select Item, I2, V2
FROM MyTable 

Union All
Select Item, I3, V3
FROM MyTable 

Union All
Select Item, I4, V4
FROM MyTable 

Union All
Select Item, I5, V5
FROM MyTable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I forgot to mention that you can sort this data. When you use a Union (or Union All) query, you only have one order by clause at the end. Like this...

SELECT Item, I1 [!]As IColumn[/!], V1 [!]As VColumn[/!]
FROM MyTable 

Union All
Select Item, I2, V2
FROM MyTable 

Union All
Select Item, I3, V3
FROM MyTable 

Union All
Select Item, I4, V4
FROM MyTable 

Union All
Select Item, I5, V5
FROM MyTable
[!]Order By IColumn, VColumn[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[woogoo]

Brilliant and a well deserved *


woogoo

 

[SQLSister]

Of course if this is inthe design stage, I would suggest normalizing the data. You really should never store data that way.

"NOTHING is more important in a database than integrity." ESquared

 

[woogoo]

Hi,

I've no control over the structure of the database in this instance, so I'm stuck with it.

Also, is it possible to have a running total included in this?

woogoo

 

[gmmastros]

is it possible to have a running total included in this?

Can you please explain what you mean? Using the sample data provided in your original question, what would you like the output to be?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[woogoo]

Hi George,

This is what I mean.

ROW 1, Item A, 0, 1.5, 1.5
ROW 2, Item B, 1, 1.6, 3.1
ROW 3, Item B, 1, 1.7, 4.8
ROW 4, Item A, 1, 1.7, 6.5
ROW 5, Item A, 2, 1.9, ...
ROW 6, Item B, 2, 2.0
ROW 7, Item A, 3, 2.1
ROW 8, Item B, 3, 2.1
ROW 7, Item B, 4, 2.5
ROW 8, Item A, 4, 2.9


woogoo

 

[gmmastros]

Running totals are usually best calculated in the front end (where you show the data). The problem is... it's inefficient to calculate it in SQL Server. There are, of course, several ways to do this. In the code I show below, I create a table variable that holds your sample data. I then create another table variable where I dump the intermediate values (the result of the union query). This table variable has an identity column, which is necessary so that we can calculate the running total.

Truth is... this is not the most efficient way to write this code either. Running totals is one of the VERY few places where a cursor performs better than a set based query. I don't have any cursors in my app, so I don't really know the syntax for cursors, which is why I am showing the set based approach. If you have a relatively small data set, this will perform well. If you have more than a couple thousand rows that you need to generate the running total for, then I suggest you re-write this to use a cursor.

Anyway, here it is....

Declare @Data 
Table   (Item VarChar(20), 
        I1 Int,
        V1 Decimal(20,10),
        I2 Int,
        V2 Decimal(20,10),
        I3 Int,
        V3 Decimal(20,10),
        I4 Int,
        V4 Decimal(20,10),
        I5 Int,
        V5 Decimal(20,10),
        I6 Int,
        V6 Decimal(20,10))

Insert Into @Data Values('Item A', 0, 1.5, 1, 1.7, 2, 1.9, 3, 2.1, 4, 2.9, 5, 4.3)
Insert Into @Data Values('Item B', 1, 1.6, 1, 1.7, 2, 2.0, 3, 2.1, 4, 2.5, 7, 3.8)

Declare @Temp Table(RowId Int Identity(1,1) Primary Key, Item VarChar(20), IColumn int, VColumn Decimal(20,10))

Insert Into @Temp(Item, IColumn, VColumn)
Select Item, I1, V1
From   @Data
Union All
Select Item, I2, V2
From   @Data
Union All
Select Item, I3, V3
From   @Data
Union All
Select Item, I4, V4
From   @Data
Union All
Select Item, I5, V5
From   @Data
Union All
Select Item, I6, V6
From   @Data
Order By V1, I1

Select A.Item, A.IColumn, A.VColumn, Sum(B.VColumn)
From   @Temp As A
       Inner Join @Temp As B
         On A.RowId >= B.RowId
Group By A.Item, A.VColumn, A.IColumn
Order By A.VColumn, A.IColumn

Select *, (Select Sum(VColumn) From @Temp As B Where A.RowId >= B.RowId)
From   @Temp As A

Notice there are 2 queries at the end that displays the results. The first uses a join condition (self join) and the other uses a sub-query. I'll leave it up to you to decide which is the most efficient for your data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom