It's nice that problem was solved. JFYI. In common, task for creating sparse matrix from plain sql normalized table is very common for OLAP. Maybe you shall read something about it if these task arised from time to time.
Best regards, Denis -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 14, 2006 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Transpose table 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] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
