0

Ok, So currently I am using a pivot which is much more efficient than using multiple joins.

Currently looking like this...

SELECT *  FROM
(
   SELECT
   s.stock_code , sui.field_value, su.field_index
   FROM stock_uda as SU
   INNER JOIN stock_uda_item as SUI ON SU.field_index = SUI.field_index
   INNER JOIN stock as S ON S.stock_id = SUI.stock_id
   WHERE sui.field_value != ''
) as A
PIVOT
(
  MAX (Field_value)
  FOR Field_index in ("846164","846165","846166","848267","945290","945291","1123994","1062208","846169","846170","846171","846172","846173","846174")
) AS MyPivotData

The string of numbers within the IN statement however can change. I can retrieve these by running the following code...

SELECT  field_index 
FROM stock_uda

I have tried assigning these and creating a string which then I used in my pivot IN statement but haven't had any luck.

Any pointers would be great!

Wolph
  • 78,177
  • 11
  • 137
  • 148
Matt The Ninja
  • 2,641
  • 4
  • 28
  • 58
  • You'll have to use dynamic SQL - take a look at this -- http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Taryn Dec 09 '14 at 14:40
  • Can i use STUFF in SQL2005? – Matt The Ninja Dec 09 '14 at 14:46
  • 1
    Yes, you sure can. You'll use `STUFF` to get the list of the values that you need as the new columns. I'd suggest trying the code in my answer on that question to see if you get the result, if you struggle then post a question with the specific dynamic sql code. – Taryn Dec 09 '14 at 14:46
  • This is essentially a duplicate - http://stackoverflow.com/questions/16759531/dynamic-pivot-in-sql-server?rq=1 – pyrospade Dec 09 '14 at 16:24

0 Answers0