Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: How to write this query

    SELECT INVENTORY_CLASS FROM V_BOM_INVENTORY

    INVENTORY_CLASS
    L
    L
    L
    L
    A
    A
    A
    A
    Q
    etc

    O/P SHOULD APPEAR AS (2 cols - L and A seperated)

    PRIMARY_INV ---------- ALTERNATE_INV
    L --------------------- A
    L -------------------- A
    L---------------------- A
    L-------------------- A
    Last edited by amitwadhawan123; 06-22-10 at 13:41.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why if there are two Ls, three As and one Q in the source data there are four Ls and four As in the result?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    magic .

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try a left outer join to the same table
    Code:
    select a.primary_inv, b.primary_inv as Alt_inv
       from ur_table a
    left outer join ur_table b
    on ????
    where???
    Dave

  5. #5
    Join Date
    Aug 2009
    Posts
    73
    my bad i have changed the post...now it has 4 l and 4 A

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  l.PRIMARY_INV
          , a.ALTERNATE_INV
    FROM    
            (
                SELECT  PRIMARY_INV     = INVENTORY_CLASS
                      , l_ordinal       = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
                FROM    V_BOM_INVENTORY
                WHERE   INVENTORY_CLASS = 'L'
            )   AS l
    FULL OUTER JOIN
            (
                SELECT  ALTERNATE_INV   = INVENTORY_CLASS
                      , a_ordinal       = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
                FROM    V_BOM_INVENTORY
                WHERE   INVENTORY_CLASS = 'A'
            )
    AS a
    ON  a.a_ordinal = l.l_ordinal

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  PRIMARY_INV     = CASE WHEN number <= inventories.l_inventory THEN 'L' END       
          , ALTERNATE_INV   = CASE WHEN number <= inventories.a_inventory THEN 'A' END
    FROM    dbo.numbers
    INNER JOIN 
            (
                SELECT  max_inventory       = MAX(COUNT(DISTINCT INVENTORY_CLASS))
                      , l_inventory         = COUNT(CASE INVENTORY_CLASS WHEN 'L' THEN 9007135  END)
                      , a_inventory         = COUNT(CASE INVENTORY_CLASS WHEN 'A' THEN 937      END)
                FROM    dbo.V_BOM_INVENTORY
                WHERE   INVENTORY_CLASS IN('L', 'A')
            )   AS inventories
    ON  numbers.number      BETWEEN 1 AND max_inventory

Posting Permissions

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