Transposing rows to columns is a PITA

Post #150,410 by ben_tilly 4/7/04 10:50:12 AM Reply	Transposing rows to columns is a PITA Tom Kyte has a solution in PL/SQL that he has presented in a couple of places. I didn't really like it. I generally handle it in a client-side language. I've found that to be fairly easy to do in Perl, and it lets the database focus on what it does best while moving the extra CPU load elsewhere. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,491 by CRConrad 4/8/04 1:52:00 AM Reply	"When the only tool you have is a hammer..." Ben writes: Tom Kyte has a solution in PL/SQL that he has presented in a couple of places. I didn't really like it. I'll check it out, anyway. (IIRC, this is the same guy you mentioned earlier in this thread [or a previous one]; so if he's good enough for you to mention twice [of which one occurrence without the disclaimer, IIRC], he's at least worth checking out for me.) I generally handle it in a client-side language. Yeah, I'd kind of like that, too but... [see below] I've found that to be fairly easy to do in Perl, Not the language I would choose, I suspect; I'd probably cobble together something in C, or whatever is available on the client's big Unix box. (Yeah, I know... But, hey, we bill per hour!) and it lets the database focus on what it does best while moving the extra CPU load elsewhere. Here's the hammer: Not, as you may have thought, that SQL (and Delphi! :-) is all I know, but that our system is implemented on that one single server(); I don't think we have access to any other. So, while I could "mov[e] the extra CPU load elsewhere", in terms of taking it out of the database... The only place I could take it, would be "elsewhere" on the same CPU. :-( (And I don't think that by talking of "client-side language" you're implying I should suck the whole table down onto my desktop PC here, right? :-) Apart from the feeble CPU, this old Win-box doesn't have all that much memory, either... Not to mention the network-bandwidth nightmare it would be!) (): Notwithstanding for the moment that "the server" is actually two separate boxes, for development and production, respectively. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #150,493 by ben_tilly 4/8/04 2:23:07 AM Reply	But that isn't the only tool that I have As you'll note from the fact that I pointed out that there is a PL/SQL solution, I'm aware of the existence of other tools. And you'll note that I used Perl because that was already in use in my situation. I could have done it in another language, but I'm not going to call out of Perl just to demonstrate how 1337 I am. In any case when I did it client-side, I sucked my result-set and then reformatted it to resend. Depending on your result set that might be an issue. For mine my "client" was on a webserver, and the result-set was enough to fit in a browser's display. Therefore the amount of data was not going to be enough to wipe me out on RAM. YMMV. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,495 by Meerkat 4/8/04 3:02:39 AM Reply	At least Perl is a good hammer to have I had a unix-hating coworker whose 'hammer' was Excel. This cow-orker still insisted they couldn't do a line count on large files because "It's more than 65000 rows, it won't load into Excel". Yep, wc -l was too difficult ("I don't do command-line"), so they loaded the file into Excel and went to the last row to get the rowcount... Sometimes, just sometimes, I feel real clever at work! John. Busy lad.
Post #150,498 by Steve Lowe 4/8/04 3:12:58 AM Reply	use that one a lot I use wc -l so much I've aliased it to just plain w Also global changes in text files..can change all occurences of a string that appears in each line of a 250K line file faster with perl or sed than the file can be loaded into a windows app. ----- Steve
Post #152,303 by broomberg 4/22/04 10:58:47 PM Reply	I don't like it Too slow for large files. And no feedback while it's working. Here's mine in Perl: \n#!/usr/local/bin/perl -w\n\nrequire 'mylib.pl'; #gets the cm - comma function\nuse strict;\n\nmy $count = 0;\nmy $MOD = 10_000;\n\nwhile (<>){\n\t$count++;\n\n\tprint STDERR "Working on [" . cm($count) . "]\\r" \n\t\tunless ($count % $MOD){\n}\n\nprint "\\n\\nCounted [" . cm($count) . "]\\n\\n";\n
Post #152,318 by Meerkat 4/23/04 2:23:05 AM Reply	No feedback? That's what top / glance is for :) John. Busy lad.
Post #150,497 by CRConrad 4/8/04 3:11:51 AM Reply	No no no, you misunderstand! The "hammer..." thing was not a reference to you and Perl; it was about me having just that one CPU to... uh, "hammer" with my processing. (No, it's not an AMD-64 CPU! :-) Perhaps a bit misplaced, in that the appropriate ending would have been, not "...then everything looks like a nail", but something like "...then you can just as well go on hammering [i.e, using it] the way you're used to". Or, IOW: I could probably increase the system's overall efficiency by moving that transposing load to some other "tool" (CPU) -- but, at least in my hands, that one "hammer" (server CPU) that I have, is probably most efficiently used for "hammering" (running SQL in the DB), rather than trying to "screw" with it... :-) That's all I meant. (As for the actual issue at hand, I'm doing this transposing in an SQL View, for the data to be loaded into the next table; this is just one step in our daily DW ETL processing. Millions and millions of rows, and the only "client"-type CPU I have unfettered access to is this NT4 desktop box with an "x86 Family 6 Model 8 Stepping 6, GenuineIntel" processor [what's that, a Pentium II? Or an early Pentium III?] at ~860 Mhz, and 512 MB RAM... That's just not on; especially as I'm using it for other stuff at the same time -- including important stuff, like talking bull on zIWT! :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]

Welcome to IWETHEY!