How to pivot query result into additional columns based on a field value

Question

So I have a view of the inventory locations and quantities of components needed for an assembly. There are two locations for which I want to get the BINS and Quantities and I want to list them side by side for each components. Here is my latest attempt:

DECLARE @INVMTL TABLE(
                  [COMPONENT]    VARCHAR(50), 
                  [LOCATION]     VARCHAR(50), 
                  [BIN]          VARCHAR(50), 
                  [QTY]          VARCHAR(50))

DECLARE @FGMTL TABLE(
                 [COMPONENT]    VARCHAR(50), 
                 [LOCATION]     VARCHAR(50), 
                 [BIN]          VARCHAR(50), 
                 [QTY]          VARCHAR(50))

INSERT INTO @INVMTL
SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
FROM vw_WorkOrdersKitLoc AS INVMTL
WHERE [INVMTL].[LOCATION] = 'INVMTL'
      AND [INVMTL].[ITEMNO] = 'ASSEMBLYNAME'

INSERT INTO @FGMTL
SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
FROM vw_WorkOrdersKitLoc AS FGMTL
WHERE [FGMTL].[LOCATION] = 'FGMTL'
     AND [FGMTL].[ITEMNO] = 'ASSEMBLYNAME'

SELECT  COALESCE([INVMTL].[COMPONENT], [FGMTL].[COMPONENT])
     , [INVMTL].[LOCATION]
     , [INVMTL].[BIN]
     , [INVMTL].[QTY]
     , [FGMTL].[LOCATION]
     , [FGMTL].[BIN]
     , [FGMTL].[QTY]
FROM @INVMTL AS INVMTL
     LEFT OUTER JOIN @FGMTL AS FGMTL ON FGMTL.COMPONENT = INVMTL.COMPONENT

This is what it returns. As you can see the extra right columns have duplicated data where it should be null. Also I know there is a shorter, neater way of doing this I simply forgot.

Here is the view returning all the data I want to reorganize

I made a spreadsheet of how I would like the retrieve this data

Thanks for any help

Answer 1

Your join isn't specific enough - you just ask the db to match this component here with that component there, which means that one ABC,1 component,location on one side will match 1:1 with one ABC,2 on the other to give ABC,1,ABC,2 but two ABC on one side and 3 on the other will end up multiplied (cartesian explosion) to become 6, if you only match on the component (ABC) from each:

(ABC,1 and ABC,2) join (ABC,3 and ABC,4 and ABC,5)
gives:
ABC,1,ABC,3
ABC,1,ABC,4
ABC,1,ABC,5
ABC,2,ABC,3
ABC,2,ABC,4
ABC,2,ABC,5

It's not really easy to see what matching logic your spreadsheet applies. It looks like the LOC is sorted alpha ascending and then row 1 is matched with row 1, 2 with 2 etc.. You need to provide a fake key to join on:

DECLARE @INVMTL TABLE(
                  [COMPONENT]    VARCHAR(50), 
                  [LOCATION]     VARCHAR(50), 
                  [BIN]          VARCHAR(50), 
                  [QTY]          VARCHAR(50),
                  fakekey INT)

DECLARE @FGMTL TABLE(
                 [COMPONENT]    VARCHAR(50), 
                 [LOCATION]     VARCHAR(50), 
                 [BIN]          VARCHAR(50), 
                 [QTY]          VARCHAR(50),
                 fakekey INT)

INSERT INTO @INVMTL
SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
     , row_number() over(partition by COMPONENT order by location) as fakekey
FROM vw_WorkOrdersKitLoc AS INVMTL
WHERE [INVMTL].[LOCATION] = 'INVMTL'
      AND [INVMTL].[ITEMNO] = 'ASSEMBLYNAME'

INSERT INTO @FGMTL
SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
     , row_number() over(partition by COMPONENT order by location) as fakekey
FROM vw_WorkOrdersKitLoc AS FGMTL
WHERE [FGMTL].[LOCATION] = 'FGMTL'
     AND [FGMTL].[ITEMNO] = 'ASSEMBLYNAME'

SELECT  COALESCE([INVMTL].[COMPONENT], [FGMTL].[COMPONENT])
     , [INVMTL].[LOCATION]
     , [INVMTL].[BIN]
     , [INVMTL].[QTY]
     , [FGMTL].[LOCATION]
     , [FGMTL].[BIN]
     , [FGMTL].[QTY]
FROM @INVMTL AS INVMTL
     FULL OUTER JOIN @FGMTL AS FGMTL ON FGMTL.COMPONENT = INVMTL.COMPONENT and fgmtl.fakekey = invmtl.fakekey

For a shorter way, you could use WITH:

WITH INVMTL as (
  SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
     , row_number() over(partition by COMPONENT order by location) as fakekey
  FROM vw_WorkOrdersKitLoc AS INVMTL
  WHERE [INVMTL].[LOCATION] = 'INVMTL'
      AND [INVMTL].[ITEMNO] = 'ASSEMBLYNAME'
), FGMTL as (
  SELECT [COMPONENT]
     , [LOCATION]
     , [BIN]
     , [QTY]
     , row_number() over(partition by COMPONENT order by location) as fakekey
  FROM vw_WorkOrdersKitLoc AS FGMTL
  WHERE [FGMTL].[LOCATION] = 'FGMTL'
     AND [FGMTL].[ITEMNO] = 'ASSEMBLYNAME'
)
SELECT  COALESCE([INVMTL].[COMPONENT], [FGMTL].[COMPONENT])
     , [INVMTL].[LOCATION]
     , [INVMTL].[BIN]
     , [INVMTL].[QTY]
     , [FGMTL].[LOCATION]
     , [FGMTL].[BIN]
     , [FGMTL].[QTY]
FROM 
    INVMTL
    FULL OUTER JOIN FGMTL 
    ON 
      FGMTL.COMPONENT = INVMTL.COMPONENT and 
      fgmtl.fakekey = invmtl.fakekey

Or define views for FGMTL etc..