How do I take the values of one record in a table and split it into multiple columns?-CodePudding

First post. Looking for some guidance, direction, and/or assistance.

I have two tables which contain the following fields I need to use:

Master Data: Master_ID (PK)
Item Data: Crate_ID, Master_ID (FK), Item_Type_ID, Item_Type_Description, Item_Date

The Item_Type_ID has several different numerical values, i.e. 10, 20, 30, 40, 50 ... 100 ... etc. Each numerical value represents a type, i.e. Veggie, Fruit, Grains, Meat, etc.

The Item_Type_Description are things like: Fruit, Veggies, Grains, Meat, etc.

The Item_Date is a single date that identifies when that particular item (based upon Item_ID) was added to the Crate.

Note that there can only ever be one unique Item_Type_ID per Master_ID. Meaning, Item_Type_ID '10' can only ever be related to Master_ID '1234' once. An Item_Type_ID can be related to many different Master_IDs, but each of those Master_IDs, it can only be related once.

The issue I am having is that I can get the combined results, but for each Item_Type_ID, a distinct record/row is being created.

Here is the code I have generated thus far, which is giving me the incorrect Results:

USE Shipping
GO

BEGIN



SELECT
    vmi.master_id
    ,CASE
        WHEN vid.item_type_id = 10 THEN vid_item_date
        ELSE NULL
    END as 'Fruit_Item_Date'
    ,CASE
        WHEN vid.item_type_id = 20 THEN vid_item_date
        ELSE NULL
    END as 'Veggie_Item_Date'
    ,CASE
        WHEN vid.item_type_id = 30 THEN vid_item_date
        ELSE NULL
    END as 'Grains_Item_Date'
    ,CASE
        WHEN vid.item_type_id = 40 THEN vid_item_date
        ELSE NULL
    END as 'Meat_Item_Date'

FROM v_master_data vmi
LEFT JOIN v_item_data vid ON vmi.master_id = vid.master_id

WHERE vid.item_type_id IN (10,20,30,40)


END
GO

Any input, pointers, assistance, direction, advice, is greatly appreciated. Even if the answer is not provided, if you could at least point me in the direction to read some material related to this scenario, view some examples, key words, etc., in order to teach myself the concept, that would be very helpful too.

Running MS SQL Server 2016 off of MS SQL Server Management Studio v18.

CodePudding user response：

Perhaps this will give you a little nudger

PIVOT

Select Master_ID
      ,Fruit_Date  = [10]
      ,Veggie_Date = [20]
      ,Grains_Date = [30]
      ,Meat_Date   = [40]
 From  (
        Select Master_ID
              ,Item_Type_ID
              ,Item_Date
         From  YourTable
       ) src
 Pivot ( max(Item_Date) for Item_Type_ID in ( [10],[20],[30],[40] ) ) pvt

Conditional Aggregation

Select Master_ID
      ,Fruit_Date  = max( case when Iten_Type_ID =10 then Item_Date end)
      ,Veggie_Date = max( case when Iten_Type_ID =20 then Item_Date end)
      ,Grains_Date = max( case when Iten_Type_ID =30 then Item_Date end)
      ,Meat_Date   = max( case when Iten_Type_ID =40 then Item_Date end)
 From  YourTable
 Group By Master_ID

A conditional aggregation offers a bit more flexibility and is often more performant.