Transform sequential key value data to row based key value data, PostgreSQL

Transform sequential key value data to row based key value data

--PostgreSQL 9.6
--'\\' is a delimiter
-- solution related to this thread:
-- https://www.delphipraxis.net/203128-tabelle-openoffice-zugreifen.html
-- export of original spreadsheet data to csv and import
-- to postgres or SQLite is required

select version() as postgresql_version;

create table tableSpreadsheetStyle(
    tssID Serial,
    tssKeyAndValue varchar(100));

insert into tableSpreadsheetStyle (tssKeyAndValue) values
    ('Ham'),('Egg'),
    ('Hase'),('Igel'),
    ('Blumen und Nelken'), 
    ('Vorkommen überall und in Klein-Asien, bitte stell sie in die Vase');
    
select 'Beispieldaten' as Kommentar;
select * from  tableSpreadsheetStyle;

select 'Transformation' as Kommentar;
select tssID, tssKeyAndValue as tssKey,
       lead(tssKeyAndValue,1) OVER (
       ORDER BY tssID)       as tssValue
from  tableSpreadsheetStyle ;

select 'Filter' as Kommentar;
select * 
  from (
        select tssID, tssKeyAndValue as tssKey,
               lead(tssKeyAndValue,1) OVER (
                    ORDER BY tssID)  as tssValue
          from tableSpreadsheetStyle 
       ) x
 where mod(x.tssID+1, 2) = 0;

drop table tableSpreadsheetStyle;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development