More efficient query or model needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

More efficient query or model needed

Author

Topic

Tim Tisseret
Starting Member

2 Posts

Posted - 2009-06-23 : 17:01:33

I have a data file with
over 200 fields so it is too much work to create a DB table w/ 200 columns.

So, I made a two tables:

Transaction Table (contains interesting fields)
item_id, file_date, quantity, price, product

Item Table (contains all fields):
item_id, field, value

I want to be able to create custom reports in Excel.
So, my idea is to create a view that combines both tables to show every single field:

FullTransaction View

select
T.item_id,
T.file_date,
T.quantity,
T.price,
T.product
(select value from Item I where I.field = 'Field 1' and I.item_id = T.item_id) as [Field 1],
...
...
(select value from Item I where I.field = 'Field 200' and I.item_id = T.item_id) as [Field 200],

from Transaction T

The problem is this query is very slow. I don't think the PIVOT operator would be applicable because the values are not all numbers. Can you offer a suggestion please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 00:34:53

MAX operator for PIVOT also works with varchar.
The drawback of PIVOT is that all columns need to be of same datatype.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources