@JeffModen - Thanks.

This view returns 486 K rows in 14 secs.

SELECT ShipmentId, MAX(CASE WHEN RefTypeId = 1 THEN Value END) AS [Client BOL Number], MAX(CASE WHEN RefTypeId = 2 THEN Value END) AS [PO #], MAX(CASE WHEN RefTypeId = 3 THEN Value END) AS [Airway Bill #],

```
MAX(CASE WHEN RefTypeId = 5 THEN Value END) AS [Client ERP Sold To], MAX(CASE WHEN RefTypeId = 6 THEN Value END) AS [Client ERP Consignee Acct], MAX(CASE WHEN RefTypeId = 7 THEN Value END) AS [Trailer #],
MAX(CASE WHEN RefTypeId = 8 THEN Value END) AS [Container #], MAX(CASE WHEN RefTypeId = 9 THEN Value END) AS [Custom BOL#], MAX(CASE WHEN RefTypeId = 10 THEN Value END) AS [Sales Order Number],
MAX(CASE WHEN RefTypeId = 11 THEN Value END) AS [Sales Name], MAX(CASE WHEN RefTypeId = 12 THEN Value END) AS [Seal Number], MAX(CASE WHEN RefTypeId = 14 THEN Value END) AS [Order Type],
MAX(CASE WHEN RefTypeId = 15 THEN Value END) AS [BOL Stop Sequence], MAX(CASE WHEN RefTypeId = 16 THEN Value END) AS [Direction Code], MAX(CASE WHEN RefTypeId = 17 THEN Value END) AS [BOL Status Code],
MAX(CASE WHEN RefTypeId = 38 THEN Value END) AS [Niteo Shipper Ref 1], MAX(CASE WHEN RefTypeId = 39 THEN Value END) AS [Client Spot Quote Number], MAX(CASE WHEN RefTypeId = 40 THEN Value END)
AS [Client Spot Quote Amount], MAX(CASE WHEN RefTypeId = 41 THEN Value END) AS [Delivery #], MAX(CASE WHEN RefTypeId = 42 THEN Value END) AS [Shipment #], MAX(CASE WHEN RefTypeId = 43 THEN Value END)
AS [Flair Shipper #], MAX(CASE WHEN RefTypeId = 44 THEN Value END) AS [HOUSE BILL OF LADING #], MAX(CASE WHEN RefTypeId = 45 THEN Value END) AS [MASTER BILL OF LADING #],
MAX(CASE WHEN RefTypeId = 46 THEN Value END) AS [ORGILL PO #], MAX(CASE WHEN RefTypeId = 47 THEN Value END) AS [ORGILL GL CODE], MAX(CASE WHEN RefTypeId = 48 THEN Value END)
AS [Carrier Spot Quote Bid #], MAX(CASE WHEN RefTypeId = 49 THEN Value END) AS [EDI BOL #], MAX(CASE WHEN RefTypeId = 50 THEN Value END) AS [OB BOL #], MAX(CASE WHEN RefTypeId = 51 THEN Value END)
AS [Pick Slip #], MAX(CASE WHEN RefTypeId = 52 THEN Value END) AS [Customer Sales Order #], MAX(CASE WHEN RefTypeId = 53 THEN Value END) AS [Customer PO], MAX(CASE WHEN RefTypeId = 54 THEN Value END)
AS [INTL BOL #], MAX(CASE WHEN RefTypeId = 55 THEN Value END) AS [INTL Entry #], MAX(CASE WHEN RefTypeId = 56 THEN Value END) AS [INTL Entry Date], MAX(CASE WHEN RefTypeId = 57 THEN Value END)
AS [INTL Cube], MAX(CASE WHEN RefTypeId = 58 THEN Value END) AS [INTL # OF CONTAINERS], MAX(CASE WHEN RefTypeId = 59 THEN Value END) AS [Temp Control], MAX(CASE WHEN RefTypeId = 60 THEN Value END)
AS [INTL $ OF SALE], MAX(CASE WHEN RefTypeId = 61 THEN Value END) AS [Shipment ID #], MAX(CASE WHEN RefTypeId = 62 THEN Value END) AS [Customer Number], MAX(CASE WHEN RefTypeId = 63 THEN Value END)
AS AETC, MAX(CASE WHEN RefTypeId = 64 THEN Value END) AS [Order #], MAX(CASE WHEN RefTypeId = 65 THEN Value END) AS [Marianna Pro #], MAX(CASE WHEN RefTypeId = 66 THEN Value END)
AS [WMBARR SHIP TO CONS ID], MAX(CASE WHEN RefTypeId = 67 THEN Value END) AS [WMBARR SOLD TO MASTER ACCOUNT], MAX(CASE WHEN RefTypeId = 68 THEN Value END) AS [BOL NUMBER],
MAX(CASE WHEN RefTypeId = 69 THEN Value END) AS [LOAD #], MAX(CASE WHEN RefTypeId = 70 THEN Value END) AS [SALES $], MAX(CASE WHEN RefTypeId = 71 THEN Value END) AS ISHAZMAT,
MAX(CASE WHEN RefTypeId = 72 THEN Value END) AS [PORT OF LOADING], MAX(CASE WHEN RefTypeId = 73 THEN Value END) AS [PORT OF DISCHARGE], MAX(CASE WHEN RefTypeId = 74 THEN Value END) AS CARTONS,
MAX(CASE WHEN RefTypeId = 75 THEN Value END) AS [ACH PAID DIRECTLY], MAX(CASE WHEN RefTypeId = 76 THEN Value END) AS [DESCRIPTION OF GOODS], MAX(CASE WHEN RefTypeId = 80 THEN Value END)
AS [MODINE INTERNATIONAL MODE], MAX(CASE WHEN RefTypeId = 81 THEN Value END) AS [Packing List #], MAX(CASE WHEN RefTypeId = 82 THEN Value END) AS [INTL PO#]
```

FROM dbo.ShipmentRefs

GROUP BY ShipmentId