Re: [sqlite] Transpose table
On Thu, 14 Dec 2006 09:53:22 - (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 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 + 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, >> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
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 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 + 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 >>
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 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 + 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, >> >> >> >> >> - >> 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] -
RE: [sqlite] Transpose table
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 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 + 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, > > > > > - > 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] -
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 + 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, - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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 + 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, - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Transpose table
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, like this example: ID Value - 1 A 1 B 1 A 3 G 3 D 5 A This should become: ID Value1 Value2 Value3 1 A B A 3 G D 5 A I can't see any way to do this fast and my VB method running on arrays was very fast. Does anybody have any suggestions how to handle this? Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
