title: Convert Column to Row
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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!</p>
  
  <pre><code>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')
  </code></pre>