Convert Column to Row

Question

I know how to do this one way but the query i have created takes about 1:30 to run and i need to optimize this to be faster due to system limitations. Ive used multiple joins of the same table to do this but was wondering if there is a shorter query or more efficient query i could use.

Ive given a sample of the query but it does have about 100 columns pulled from a rows on the OrderItemFormDateFields table referenced below. Ive created columns and i get the results i need, but again, its slow. Let me know if you need any more detailed info about the tables.

I guess im basically looking for a way to dynamically populate rather than list out each column. Ive seen a few different ways with pivots and case statements but im not very good with pivots yet and case seemed like it would be even more inefficient. Thanks!

SELECT
OrderNumber
,Product
,OrderDate
,   oifd1.value as  'ADD_SUBJ_ADDRESS'
,   oifd2.value as  'ADD_SUBJ_BATHS'
,   oifd3.value as  'ADD_SUBJ_BEDROOMS'
,   oifd4.value as  'ADD_SUBJ_DATE'
,   oifd5.value as  'ADD_SUBJ_GLA'
FROM(
SELECT CAST(oi.orderid as VARCHAR(MAX))+'.'+CAST(oi.orderitemid as VARCHAR(MAX))     as      OrderNumber
,p.abbreviation as product, o.orderdate
FROM OrderItems oi
join products p on p.productid = oi.productid
join orders o on o.orderid = oi.orderid)x
left    join    orderitemformdatafields     oifd1   on      oifd1.orderreference    =   OrderNumber and oifd1.fieldname in  (     'SUBJ_STREET_ADDR')
left    join    orderitemformdatafields     oifd2   on      oifd2.orderreference    =   OrderNumber and oifd2.fieldname in  (      'ADD_SUBJ_BATHS')
 left   join    orderitemformdatafields     oifd3   on      oifd3.orderreference    =   OrderNumber and oifd3.fieldname in  (      'ADD_SUBJ_BEDROOMS')
 left   join    orderitemformdatafields     oifd4   on     oifd4.orderreference =   OrderNumber and oifd4.fieldname in  (     'ADD_SUBJ_DATE')
left    join    orderitemformdatafields     oifd5   on      oifd5.orderreference    =   OrderNumber and oifd5.fieldname in  (     'ADD_SUBJ_GLA')

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

One of your problems is you're scanning the whole table five times for the five different values of fieldname. If you're not indexing that column, you'd better.

Also you'd better index on OrderNumber.

If you're calling this query from a program (such as a web page), it might be easier to "pivot" the data in that program. Get the data, write it into an array, then read it back as columns instead of rows. (That assumes that the data isn't enormous.)

Edited to add some links regarding indexing:

edited May 23 '17 at 11:49

Community

1
1

answered Aug 29 '12 at 20:59

egrunin

24,650
8
50
93

can you explain what you mean by index? or point me in the right direction somewhere online? not looking for you to solve my issues, im really trying to learn here – wondergoat77 Aug 29 '12 at 21:09

score 1 · Accepted Answer · answered Aug 29 '12 at 21:09

1

hope this will help you.

select * from (
SELECT CAST(oi.orderid as VARCHAR(MAX))+'.'+CAST(oi.orderitemid as VARCHAR(MAX))     as      OrderNumber
,p.abbreviation as product, o.orderdate
,oifd.value as value
,oifd.fieldname as fieldname
FROM OrderItems oi
join products p on p.productid = oi.productid
join orders o on o.orderid = oi.orderid
left join orderitemformdatafields     oifd   on  oifd.orderreference = CAST(oi.orderid as VARCHAR(MAX))+'.'+CAST(oi.orderitemid as VARCHAR(MAX))
) as p
PIVOT
(
    MAX(value) FOR fieldname in ([SUBJ_STREET_ADDR],[ADD_SUBJ_BATHS],[ADD_SUBJ_BEDROOMS],[ADD_SUBJ_DATE],[ADD_SUBJ_GLA])
)AS pvt

answered Aug 29 '12 at 21:09

Pradeeshnarayan

1,235
10
21

It does THANK YOU!!! I have had so much trouble understanding pivots but this helps a lot. Why do you state MAX(Value) though? these fields should only have 1 value per field per order number, so is that just a requirement of syntax or is that for a purpose? – wondergoat77 Aug 29 '12 at 21:28
i just put this with my 100+ rows to pivot and reduced my query time from 1:30-ish to under a minute! thanks again – wondergoat77 Aug 29 '12 at 21:32
1

@wondergoat77 pivot needs an aggregate function to handle multiple values. You can use any aggregate function(sum/max/min..) there. – Pradeeshnarayan Aug 29 '12 at 21:36

Convert Column to Row

2 Answers2