Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Crosstab query help

    I have the following query that yields the results shown below:

    Code:
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'ATL'
    			AND
    				p_acdcode	<> 'D'
    UNION
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'LAN'
    			AND
    				p_acdcode	<> 'D'
    UNION				 
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'NEW'
    			AND
    				p_acdcode	<> 'D'
    UNION
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'ROC'
    			AND
    				p_acdcode	<> 'D'
    UNION				
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'SEA'
    			AND
    				p_acdcode	<> 'D'
    UNION
    SELECT item, loc, p_pdcstockingind
    			FROM
    				stsc.sku
    			WHERE
    				item		= '000188ALU'
    			AND
    				loc		= 'TOR'
    			AND
    				p_acdcode	<> 'D'
    Code:
    item     Loc             P_pdcstockingind
    --------------------------------------------------
    000188alu      Atl                        N
    000188alu      Lan                       N
    000188alu      New                      N
    000188alu      Roc                       S
    000188alu      Sea                       N
    000188alu      Tor                        N

    How can I get this to look like this?

    Code:
    ITEM            ATL    LAN    NEW    ROC    SEA    TOR
    ----------------------------------------------------------
    000188alu     N        N         N       S         N       N

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    I am going to assume here that you know the locations that are present - ok so this is for static columns, and not dynamic ones:

    Code:
    select item, 
             decode(loc, 'ATL', p_pdcstockingind) atl,
             decode(loc, 'LAN', p_pdcstockingind) lan,
             decode(loc, 'NEW',p_pdcstockingind) new,
             decode(loc, 'ROC', p_pdcstockingind) roc,
             decode(loc, 'SEA', p_pdcstockingind) sea,
             decode(loc, 'TOR', p_pdcstockingind) tor
    from stsc.sku
    where item = '000188ALU' and
    p_acdcode != 'D'
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Your assumption is correct. This query however gives me output on 7 lines, not all on a single line.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use aggregation to squeeze into one line:

    Code:
    select item, 
             max(decode(loc, 'ATL', p_pdcstockingind)) atl,
             max(decode(loc, 'LAN', p_pdcstockingind)) lan,
    ...
    from stsc.sku
    where item = '000188ALU' and
    p_acdcode != 'D'
    group by item;

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Perfect. Thanks!

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Thats what happens when I don't test myself first - Thanks andrew
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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