-1

I have two tables, Order and Order_Tag.

There are one or more entries for each Order in the Order_Tag table.

Order table:

OrderKey OrderNumber
1 ABCD
2 CDEF
3 GHIJ

Order_Tag table:

OrderKey TagName TagValue
1 IntemNumber 00001
1 ItemDescription Desc1
1 ItemWeight 12.5
2 IntemNumber 00002
2 ItemDescription Desc2
2 ItemWeight 99
3 IntemNumber 00003
3 ItemDescription Desc3
3 ItemWeight 111

How can I achieve this output:

OrderKey OrderNumber IntemNumber ItemDescription ItemWeight
1 ABCD 00001 Desc1 12.5
2 CDEF 00002 Desc2 99
3 CDEF 00003 Desc3 111
Dale K
  • 25,246
  • 15
  • 42
  • 71
Don Lewis
  • 87
  • 1
  • 7
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Mar 18 '21 at 07:18
  • I tried to join the Order_Tag table three times. There is a easier way to do this with a PIVOT. – Don Lewis Mar 18 '21 at 07:49
  • Does this answer your question? [Understanding PIVOT function in T-SQL](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) You can also do it with [conditional aggregation](https://stackoverflow.com/questions/48405660/how-to-apply-conditional-aggregate-function-in-a-pivot) – Charlieface Mar 18 '21 at 09:45

1 Answers1

1

You can use conditional aggregation. I would do so using APPLY:

select o.*, ot.*
from orders o cross apply
     (select max(case when ot.tagName = 'IntemNumber' then TagValue end) as IntemNumber,
             max(case when ot.tagName = 'ItemDescription' then TagValue end) as ItemDescription,
             max(case when ot.tagName = 'ItemWeight' then TagValue end) as ItemWeight
      from order_tag ot
      where ot.OrderKey = o.OrderKey
     ) ot;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786