I am programming my Stored Procedures and Views for performance, since we are dealing with millions of records. I know this question is going to sound like I do NOT want to do it this way, but in this case, it may work...
I have an Invoice LineItems table which will only ever contain up to 3 items. It will NEVER contain any more than that. We only deal with 3 items/services, and it will never change. So let's call them A, B, and C.
I have two options and I need to know if number (1) is doable, and/or if it will be faster, and more efficient to do (2).
(1)
Create a view that has 4 columns: Invoice Number, Product A Price, Product B Price, and Product C Price. I want to do it this way because it will work perfectly for a Stored Procedure I wrote which is running very quickly and does exactly what I need...
(2)
Create 3 views that get Product A, Product B Price, and Product C Price, and then just join them in another view. Is this going to be too much overhead? I mean, it will be running 4 views essentially... What do you think?
Here is something I wrote hoping it would work... but no go. The result was not what was expected:
Expand|Select|Wrap|Line Numbers
- SELECT
- [invoice number],
- CASE WHEN [Item Number] = 'A' THEN [Price]) END
- AS APRICE,
- CASE WHEN [Item Number] = 'B' THEN [Price]) END
- AS BPRICE
- FROM
- [Invoice Lineitems]
- GROUP BY
- [invoice number]