On Thu, 14 Dec 2006 09:53:22 -0000 (GMT), you wrote: >Hi Denis, > >Yes, it is a one-off action and the only purpose is to present the data >into and Excel sheet in a more readable way. >I had done your suggestion in VBA, but I thought it was a bit slow >and wondered if there was a better way.
If Excel is the target anyway I guess the fasted way to do this is to use the transpose option of the paste-special function in Excel itself. >I have just found a possible way to do this and maybe it is faster. >Say I have a table with an ID column and 3 other columns. >The data in these other columns need to be grouped by ID number, so > >ID col1 col2 col3 > >would become: > >ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > >etc. where the maximum number of fields will be determined by the >maximum number of records for one ID number > >Now I found that if I do: >select >ID, >col1, >col2, >col3 >from >table >group by >ID > >Then it will always pick up the row that comes last in the group of >ID numbers. This might actually be faster than doing a subquery with MAX. > >Now if I run the above and move the data to a new table, say table2 and >then run a query like this: > >select >t1.ID, >t1.col1, >t1.col2, >t1.col3 >from >table1 t1 inner join table2 t2 on >(t1.ID = t2.ID) >where >t1.col1 < t2.col1 >group by >t1.ID > >Then I will get the rows (if there was a row left)in the ID group >that comes second from last, so > >ID >1 >1 >1 >1 < will get this one >1 > >If I keep repeating this in a VBA loop and then join the tables I would >get my output. Not sure it is faster, but I think it might. >Will see. > > >RBS > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? >> So there is a pseudocode describing one of possible approaches. To >> convenient transformation SQLite is not enough for me, I suggest to use >> any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC >> LIMIT 1 >> Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value<n> TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert data >> to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value for >> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away >> from SQL logic. >> >> Regards, Denis >> >> -----Original Message----- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first row >> where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -----Original Message----- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM +0000 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There is >>>one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a horizontal >> one, >> <snip> -- ( Kees Nuyt ) c[_] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
