Re: [sqlite] Transpose table

2006-12-14 Thread Kees Nuyt
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

2006-12-14 Thread Denis Povshedny
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

2006-12-14 Thread bartsmissaert
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

2006-12-14 Thread bartsmissaert
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

2006-12-13 Thread RB Smissaert
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

2006-12-13 Thread Darren Duncan
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]
-