Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    12

    Unanswered: Self-Join to split cost in different columns by category

    This is probably a very simple problem, but I'm new in SQL and I can't solve it in simple way, I've a table similar to the one below, with a SKU, Category and Cost, and need using a simple select command, split the cost in two columns one for each category (1,2), I used a self-join, and it works, but it doesn't show values not equal in both categories


    Declare @Tmp_SKUCatValue Table(
    SKU char(7)
    ,Cetegory Int
    ,Unit_cost Decimal
    );
    INSERT INTO @Tmp_SKUCatValue (SKU, Cetegory,Unit_cost)
    Values
    ('sku-001',1,120)
    ,('sku-002',1,126)
    ,('sku-003',1,132)
    ,('sku-004',1,138)
    ,('sku-005',1,144)
    ,('sku-006',1,333)
    ,('sku-008',3,333)
    ,('sku-001',2,222)
    ,('sku-002',2,228)
    ,('sku-003',2,234)
    ,('sku-004',2,240)
    ,('sku-005',2,246)
    ,('sku-007',2,444)
    select T1.SKU,T1.Unit_cost aS UCost_Cat1,T2.Unit_cost aS UCost_Cat2 from @Tmp_SKUCatValue T1 FULL OUTER JOIN @Tmp_SKUCatValue as T2 on T1.SKU=T2.SKU
    WHERE T1.Cetegory=1 AND T2.Cetegory=2
    The result is as
    SKU----------UCost_Cat1-----UCost_Cat2
    sku-001------120--------------222
    sku-002------126--------------228
    sku-003------132--------------234
    sku-004------138--------------240
    sku-005------144--------------246
    -----------------------------------------------------------
    but missing the following lines,
    SKU----------UCost_Cat1-----UCost_Cat2
    sku-006------333--------------null
    sku-007------null--------------444
    Is ok to not show sku-008 as it is not part of category 1 or 2

    Thanks for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT SKU
         , Max(CASE WHEN Cetegory = 1 THEN Unit_cost END) As UCost_Cat1
         , Max(CASE WHEN Cetegory = 2 THEN Unit_cost END) As UCost_Cat2
    FROM   Tmp_SKUCatValue
    WHERE  Cetegory IN (1, 2)
    GROUP
        BY SKU
    ;
    Howzat?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Another option...
    Code:
    ; WITH SKUs AS (
      SELECT DISTINCT
             SKU
      FROM   Tmp_SKUCatValue
      WHERE  Cetegory IN (1, 2)
    )
    SELECT SKUs.SKU
         , T1.Unit_cost As Unit_cost1
         , T2.Unit_cost As Unit_cost2
    FROM   SKUs
     LEFT
      JOIN Tmp_SKUCatValue As T1
        ON T1.SKU = SKUs.SKU
       AND T1.Cetegory = 1
     LEFT
      JOIN Tmp_SKUCatValue As T2
        ON T2.SKU = SKUs.SKU
       AND T2.Cetegory = 2
    ;
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •