CODEWITHSUNDEEP
sqlsql-servert-sql
Andrew Fry
Andrew Fry

Reputation: 23

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

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

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.

Running SQL Server 2016, accessed via SQL Server Management Studio v18.

Upvotes: 0

Views: 67

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps this will give you a little nudge

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.

Upvotes: 1

Related Questions