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]
-----------------------------------------------------------------------------
