I studied thoroughly again the pattern of the stacked data in a single column and found below workaround. I used blank rows as anchor points to encode the record number. I marked preceding and succeeding blank rows in the range the encapsulated values can be marked as one record.
I have taken the small set of sample data for testing purpose with variable number of fields per record.
Below is the sample data in the file test.csv, first 2 rows are blank and every subset (actual record) is also followed by 2 blank rows.
item11
item12
item21
item22
item23
item31
item32
item33
item34
---- step1 # importing the sample data from the file…
.mode csv
.import test.csv test
---- step2(i) # adding row numbers and then deleting one of 2 blank rows so that every single blank row becomes the starting and ending point of the record.
DROP TABLE IF EXISTS test1;
CREATE TABLE IF NOT EXISTS test1 AS SELECT *, ROW_NUMBER() OVER() rownum FROM test ;
DELETE FROM test1 WHERE item='' AND MOD(rownum,2)=0;
---- step2(ii) # inserting one blank row at the end for last record ending point and deleting row number column as it has become inconsistent due to deleting blank rows.
INSERT INTO test1(item) VALUES('');
ALTER TABLE test1 DROP rownum;
---- step2(iii) # adding back the row numbers.
DROP TABLE IF EXISTS itemrow;
CREATE TABLE IF NOT EXISTS itemrow AS SELECT *, ROW_NUMBER() OVER() rownum FROM test1 ;
--- result from step 2 looks like below.
.mode csv
.h on
SELECT * FROM itemrow;
item,rownum
"",1
item11,2
item12,3
"",4
item21,5
item22,6
item23,7
"",8
item31,9
item32,10
item33,11
item34,12
"",13
"",14
--- there are 2 blank rows at the end but that hardly affect the process.
---- step3 # creating the range table as a view which consists of 3 columns;
(i) record number (recno) which is the filtered blank rows because each blank row represents the starting point of a record hence each blank row is a record number.
(ii) range from (rngfrm) which is the row numbers of all filtered blank rows.
(iii) range to (rngto) which is the row numbers of all filtered blank rows skipping first blank row because second blank row is the ending point of first record and close the range. Same way for second record, second blank row is starting point and third blank row is ending point. Query in action can explain it well.
DROP VIEW IF EXISTS range;
CREATE VIEW IF NOT EXISTS range AS
WITH
range1 AS (SELECT ROW_NUMBER() OVER() recno, rownum rngfrm FROM itemrow WHERE item=''),
range2 AS (SELECT ROW_NUMBER() OVER() recno, rownum rngto FROM itemrow WHERE item='' AND rownum>1)
SELECT recno, rngfrm, rngto FROM range1 INNER JOIN range2 USING(recno);SELECT recno, rngfrm, rngto FROM range1 INNER JOIN range2 USING(recno);
--- here is the look of range view showing record number, its range from and to points.
SELECT * FROM range;
recno,rngfrm,rngto
1,1,4
2,4,8
3,8,13
4,13,14
---- step4 # imposing the range table to the data table to achieve record number and field number of each row.
DROP TABLE IF EXISTS itemrecfld;
CREATE TABLE IF NOT EXISTS itemrecfld AS SELECT item, recno, ROW_NUMBER() OVER(PARTITION BY recno) fldno FROM itemrow JOIN range WHERE rownum BETWEEN rngfrm AND rngto AND item<>'';
--- here looks table with record number and field number.
SELECT * FROM itemrecfld;
item,recno,fldno
item11,1,1
item12,1,2
item21,2,1
item22,2,2
item23,2,3
item31,3,1
item32,3,2
item33,3,3
item34,3,4
---- step5 # final step for pivoting the data based on field number grouped by record number whick gives the desired proper table in multiple colums. However there is no dynamic way to attribute column name and update data into relevant field on the fly, you have to hardcode each column separately which is not a big task. You can hardcode one column one extra based on the maximum of fields.
SELECT
MAX(CASE WHEN fldno=1 THEN item END) col1,
MAX(CASE WHEN fldno=2 THEN item END) col2,
MAX(CASE WHEN fldno=3 THEN item END) col3,
MAX(CASE WHEN fldno=4 THEN item END) col4,
MAX(CASE WHEN fldno=5 THEN item END) col5,
FROM itemrecfld GROUP BY recno;
---voila, done. See.
col1,col2,col3,col4,col5,col6
item11,item12,,,,
item21,item22,item23,,,
item31,item32,item33,item34,,