Have tested this now and it seems to work fine. Faster as well than my old method.
RBS > 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. > > 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> >> >> ------------------------------------------------------------------------ >> ---- >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> ------------------------------------------------------------------------ >> ---- >> - >> >> >> >> >> ------------------------------------------------------------------------ >> ----- >> To unsubscribe, send email to [EMAIL PROTECTED] >> ------------------------------------------------------------------------ >> ----- >> >> >> ----------------------------------------------------------------------------- >> To unsubscribe, send email to [EMAIL PROTECTED] >> ----------------------------------------------------------------------------- >> >> >> > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
