1

I'm looking to pivot my key-value pairs from two tables into a pivoted view. In my example below, I'm showing 5 of the Key records and their values from the data table. I do, in fact, have over 600 of the Key records and various views and reports will need to be created from certain Key fields in the Definition table, so I will be expanding whatever answers I can get here into a more complex query. For now, though, I just need to display data.

I have the following data:

OrderDefinition Table:

OrderDefsID   OrderDefName
--------------------------------------
1             diam_aim
2             len_aim
3             len_htol
4             len_ltol
5             wgt_aim

OrderData table:

OrderHeaderID   OrderDefID  OrderDefValue
---------------------------------------------------
1               1           13.000
1               2           8.00
1               3           8.84
1               4           8.44
1               5           3611
2               1           13.000
2               2           7.98
2               3           8.43
2               4           7.32
2               5           3601
3               1           10.000
3               2           7.93
3               3           8.03
3               4           4.48
3               5           2115

What I would like to see is this:

OrderHeaderID   diam_aim   len_aim   len_htol   len_ltol   wgt_aim
--------------------------------------------------------------------------
1               13.000     8.00      8.84       8.44       3611
2               13.000     7.98      8.43       7.32       3601
3               10.000     7.93      8.03       4.48       2115

I have looked around for examples to learn from, but I can find very little dynamic examples that include a join. I don't want to do any math at all, just display the data by pivoting it and list the Key-Value pairs by OrderHeaderID.

I'm thinking this will require a Pivot, but if anyone knows another method, I'd love to learn it.

potashin
  • 44,205
  • 11
  • 83
  • 107
Impshial
  • 25
  • 4
  • 2
    Ok, so you've seen examples - have you tried writing the code? You should really include your attempts at doing this even if it isn't dynamic at first. [This question joins and the answers include a dynamic version](http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql) – Taryn Jan 08 '15 at 15:58
  • I guess I didn't look hard enough. I really appreciate the link. I got the solution right away. – Impshial Jan 08 '15 at 16:18
  • If that works, then I'll close this as a duplicate. If you have issues with that code, then edit the question with the code and we can help you resolve this. – Taryn Jan 08 '15 at 16:18
  • @bluefeet : Was that you who downvoted my answer? If I am right, please, explain were I was wrong, it's like the second `PIVOT` challenge I'm doing. – potashin Jan 08 '15 at 16:34
  • 1
    @notulysses No it wasn't me. – Taryn Jan 08 '15 at 16:37

0 Answers0