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

    Unanswered: crosstab query help

    I am trying to use a crosstab query to query this table (promo_params):

    Code:
    PLNRCODE	PARAM	     VALUE	INSERT_DATETIME
    M32	        Abc	     A	        05/17/2007 09:26:17
    M32	        Abc	     B	        05/17/2007 09:26:17
    M32	        Abc	     C	        05/17/2007 09:26:17
    M32	        DaysOfSupply 90	        05/17/2007 09:26:17
    M32	        DlrGrp	     TRUCKENTR	05/17/2007 09:26:17
    M32	        PromoCode    MAY2007ALL	05/17/2007 09:26:17
    M32	        PromoCode    MAY2007US	05/17/2007 09:26:17
    with the output looking like this:

    Code:
    DLRCODE   GROUP   DAYS   PLNRCODE
    T100      GRP-A   90     M32           
    T200      GRP-A   90     M32           
    T300      GRP-A   90     M32           
    T400      GRP-A   90     M32           
    T500      GRP-A   90     M32
    The part that is stumping me is that the DLRCODE value comes from another table. Here's the query that pulls the codes: SELECT loc FROM loc WHERE p_dlrgrp = 'GRP-A'

    I can't seem to figure out how to do this because this query returns multiple rows.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm looking at it and all I can say is: there's no way to get the OUTPUT from the INPUT.

    Instead of posting such a mess, you'd better provide CREATE TABLE and INSERT INTO sample data statements; then we'll be able to create testing environment and try to do something about it.

    However, it is quite late here so - perhaps I'm not seeing obvious. If that's the case, I apologize.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    From what I can see you are looking for the VALUE from the promo_params table where the PARAM is DaysOfSupply which joins another table (unspecified) that has the DLRCODE in it. However I don't see where your JOIN is going to occur for the PLNRCODE (as you haven't given a table layout). And I would also say that your tables look horrid.... no offense intended. The PARAM and VALUE columns in promo_params make me shudder.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I concur with Littlefoot.

Posting Permissions

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