Getting data from several rows onto one

CyndyMW · #1 (**permalink**) 01-04-06, 17:05

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

sun4u · #2 (**permalink**) 01-05-06, 05:15

I addressed similar problem some time back using rank() or denserank() and i was able to transpose using single SQL statement only.

urquel · #3 (**permalink**) 01-05-06, 10:39

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?

CyndyMW · #4 (**permalink**) 01-05-06, 12:04

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.

Wim · #5 (**permalink**) 01-06-06, 04:38

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.

Peter.Vanroose · #6 (**permalink**) 01-06-06, 04:43

Quote:

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;

sun4u · #7 (**permalink**) 01-06-06, 09:12

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;

CyndyMW · #8 (**permalink**) 01-06-06, 12:22

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

Peter.Vanroose · #9 (**permalink**) 01-11-06, 04:13

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/ansi-sql/1207767-concatenation-group-problem.html#post4509777 for a similar problem.