Help with PIVOT query

Transact-SQL
1

Hi everyone,

It's my first post here, hopefully I'm able to get some assistance. I will be upfront that I'm not 100% sure that PIVOT is what I need here, but I'm guessing...any input is greatly appreciated.

I've got a table that looks like this...


|+-------------+--------------+----------+|
|| METADATASET |     NAME     |  VALUE   ||
|+-------------+--------------+----------+|
||      109398 | WO_NB        | WOCAT08  ||
||      109398 | ITEM_NB      | 56741575 ||
||      109398 | CONTAINER_NB | 2        ||
|+-------------+--------------+----------+|

I am writing a trigger that is being used to print labels for containers. I need to be able to select the container number(CONTAINER_NB) that matches the metadataset number of the work order(WO_NB), and item number(ITEM_NB).

Can someone help me with the best way to do this?

2 
SELECT c.VALUE AS CONTAINER_NB
FROM (SELECT * FROM tbl WHERE NAME = 'CONTAINER_NB') c
INNER JOIN (SELECT * FROM tbl WHERE NAME = 'WO_NB' AND VALUE = @wo_nb) wo
ON wo.METADATASET = c.METADATASET
INNER JOIN (SELECT * FROM tbl WHERE NAME = 'ITEM_NB' AND VALUE = @item_nb) i
ON i.METADATASET = wo.METADATASET
3 

SELECT 
    /*METADATSET,*/
    MAX(CASE WHEN NAME = 'WO_NB' THEN VALUE END) AS WO_NB,
    MAX(CASE WHEN NAME = 'ITEM_NB' THEN VALUE END) AS ITEM_NB,
    MAX(CASE WHEN NAME = 'CONTAINER_NB' THEN VALUE END) AS CONTAINER_NB
FROM dbo.table_name
GROUP BY METADATASET