Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Unanswered: Getting data from several rows onto one

    Hi All,

    This is a low-level priority (but needs to get done) problem that has been bugging me for weeks. I'm finally giving in and asking for suggestions.

    We have a report which comes through a third party software package that can natively query DB2/SQL. Since I can use DB2/SQL, the third party sofrware is a non-issue. The report has rows with a relationship of one item to many different pick locations in the warehouse. For example:

    ProductA Location 2342
    ProductA Location 0790
    ProductB Location 9654
    ProductC Location 2732
    ProductC Location 8469
    ProductC Location 2678

    ... and so on. The same product can have one to infinitely many locations, but for practical purposes don't get much higher than five and never ten. What my boss wants to see is something more like this:

    ProductA Location 2342 Location 0790
    ProductB Location 9654
    ProductC Location 2732 Location 8469 Location 2678

    Of course, I'm really working with thousands of records, but this sample data set simplifies it.

    Essentially, what I need to do is transpose many records on to one, but I don't have the luxury of the same number of records per product.

    What I've tried is to write a function in SQL Server (cheating, yes, but I am more familiar with that environment) that will grab the Nth location where the field and the integer are passed as parameters. It worked, but it was so labor intensive that it ran through the whole lunch hour and ate up most of the server. Next I tried this in DB2, changing up the syntax to be DB2 syntax, and it hasn't ever worked at all. Once again, I fear a performance nightmare. The user is accustomed to seeing it in the first format almost instantaneously on his screen, and won't want to wait forever to run the report no matter how nicely it comes up.

    So I pose the question: Does anyone have any ideas for addressing this problem? Is there some kind of "transpose" operation out there that's similar to the one in Excel? I feel like I'm looking for a needle in a haystack.

    Thanks in advance,
    Cynthia

  2. #2
    Join Date
    Dec 2005
    Posts
    18
    I addressed similar problem some time back using rank() or denserank() and i was able to transpose using single SQL statement only.

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    Do you have a way of limiting the number of locations for a specific product?

    How do you determine the order of locations for a specific product? (In other words, what makes Location 2342 come before Location 0790 for Product A?)

    Have you tried the obvious alternative.... talk the user into utilizing the data in row format instead of column format?

  4. #4
    Join Date
    Jan 2006
    Posts
    4
    Thanks to you both for your reply!

    We do want to see all locations for a given product, so limiting them wouldn't be an option in this case. However, I've never seen it go over five, and can't imagine it going over ten.

    Currently I don't have any ordering built in, but I could do that by ordering by that field, such as ORDER BY Location ASC.

    Unfortunately, the person who wants to see it done is my boss's boss, and I have no personal contact with the user. Since I joined this company merely two months ago, I want to show that I can find a way to do this.

    The rank() and denserank() functions do look promising, as I have been researching those thanks to sun4u. I can't yet think of a way to implement that so that I can rank them by location within a product and then within that product, have one field that looks for a rank of 1, a second field that looks for a rank of 2... etc. I have some brainstorming to do on this, and I'm open to suggestions.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I had this problem once, but here I had to write everything into one field.
    I made an update loop that each time it ran, added a new value to this denormalised field DENORM_FIELD. The loop would exit if there were no more updates performed by DB2. I used MAX(normalised field NORM_FIELD), excluding previously added values, to select a new value from the normalised table TABLE_ORIG. In the dernormalised field, the original values are separated by ','.

    Code:
    DO
    
    UPDATE "TABLE_UPD"
    // add a new value to the denormalised field. The first time this 
    // is run, DENORM_FIELD IS NULL, so use COALESCE or it will stay NULL
    SET DENORM_FIELD = COALESCE(RTRIM(DENORM_FIELD) || ', ' , '') ||
    // select a new value, arbitrary use of MAX, 
    //  excluding previously used values
    	(SELECT MAX(RTRIM(CHAR("TABLE_ORIG"."NORM_FIELD")))
    	FROM "TABLE_ORIG" 
    	WHERE "TABLE_ORIG"."PK" = "TABLE_UPD"."FK" and  
    		LOCATE(RTRIM(CHAR("TABLE_ORIG"."NORM_FIELD"),
    			COALESCE(DENORM_FIELD, '')) = 0
    		)
    WHERE EXISTS 
    // only update when there are still values that have to be added
    	(SELECT "TABLE_ORIG"."NORM_FIELD"
    	FROM "TABLE_ORIG" 
    	WHERE "TABLE_ORIG"."PK" = "TABLE_UPD"."FK" and  
    		LOCATE(RTRIM(CHAR("TABLE_ORIG"."NORM_FIELD"), 
    			COALESCE(DENORM_FIELD, '')) = 0
    					)
    USING i_tr_sql;
    If i_tr_sql.Sqlcode = -1 then
    	MessageBox("Error", "DB2 error in fw_export for w_send_aang ~n" + &
    	"Update TABLE_UPD SET DENORM_FIELD " + i_tr_sql.SQLErrtext)
    	return -1
    end if
    
    // repeat loop untill DB2 reports: no records where found 
    LOOP UNTIL i_tr_sql.SQLDBcode = 100
    But this requires a preprocessing step. If you are working on a datawarehouse, that's no problem. Otherwise you will have to convert the above loop into a recursive SQL.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Is there some kind of "transpose" operation out there that's similar to the one in Excel?
    The short answer is: no.

    Do you have the option of post-processing the DB2 output with a program or script? E.g., the following perl script would do what you want:
    Code:
    #! perl -n
    ($n,$r)=m/(\S+)(.*)/;
    print "\n$n" unless ($n eq $p);
    $p=$n;
    print $r;
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Dec 2005
    Posts
    18
    There you go...

    I am sure you can optimise this query using temporary table or MQT !

    create table test (prod_id varchar(20), location varchar(20), loc_id varchar(20));

    insert into test ('ProductA','Locaion','2342'), ('ProductA','Locaion','07596'), ('ProductA','Locaion','2342'), ('ProductA','Locaion','07596'), ('ProductB','Locaion','9645'), ('ProductC','Locaion','2796'), ('ProductC','Locaion','8526'), ('ProductC','Locaion','2698');

    select t.prod_id, c1.location, c1.loc_id, c2.location, c2.loc_id, c3.location, c3.loc_id
    from (select distinct prod_id from test) t
    left outer join (select test.*, rank() over (partition by prod_id order by Prod_id, loc_id) as rn from test) c1 on t.prod_id = c1.prod_id and c1.rn = 1
    left outer join (select test.*, rank() over (partition by prod_id order by Prod_id, loc_id) as rn from test) c2 on t.prod_id = c2.prod_id and c2.rn = 2
    left outer join (select test.*, rank() over (partition by prod_id order by Prod_id, loc_id) as rn from test) c3 on t.prod_id = c3.prod_id and c3.rn = 3;

  8. #8
    Join Date
    Jan 2006
    Posts
    4

    Smile

    These are all great suggestions; thank you! I may end up trying them all, but sun4u's suggestion looks easiest to start with.

    I appreciate everyone's help. This is a great community!

    Cynthia

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Here's an other (pure SQL) solution:
    Code:
    SELECT A.prod_ID, 'Location ' ||
           min(
           A.loc_ID ||
           CASE WHEN B.loc_ID IS NULL THEN ' ' ELSE ', ' || B.loc_ID ||
             CASE WHEN C.loc_ID IS NULL THEN ' ' ELSE ', ' || C.loc_ID
           END END) AS locations
    FROM   tbl AS A
           LEFT OUTER JOIN tbl AS B
           ON A.prod_ID = B.prod_ID AND A.loc_ID < B.loc_ID
           LEFT OUTER JOIN tbl AS C
           ON A.prod_ID = C.prod_ID AND B.loc_ID < C.loc_ID
    GROUP BY A.prod_ID
    See the thread in http://www.dbforums.com/showthread.php?postid=4509777 for a similar problem.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Apr 2004
    Posts
    54
    Take a DB2 Cook book from the WEB.
    http://mysite.verizon.net/Graeme_Bir...k/DB2V82CK.PDF

    Look at section Normalize/Denormalize data

Posting Permissions

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