assuming you have a fixed number of bin values, you can do something like this:
CREATE TABLE myTable (component REAL,bin INTEGER,prd INTEGER); INSERT INTO myTable VALUES (2.1,1,217); INSERT INTO myTable VALUES (6.5,4,217); INSERT INTO myTable VALUES (7.1,3,217); INSERT INTO myTable VALUES (7.6,5,217); INSERT INTO myTable VALUES (7.7,5,217); INSERT INTO myTable VALUES (1.3,2,217); INSERT INTO myTable VALUES (1.1,1,298); INSERT INTO myTable VALUES (3.1,1,298); INSERT INTO myTable VALUES (6.2,2,298); INSERT INTO myTable VALUES (7.3,5,298); INSERT INTO myTable VALUES (8.1,3,298); INSERT INTO myTable VALUES (8.4,4,298); INSERT INTO myTable VALUES (1.1,5,298); SELECT distinct m0.prd, m1.component ||'|'|| m2.component ||'|'|| m3.component ||'|'|| m4.component ||'|'|| m5.component as "combo" FROM myTable as m0 LEFT JOIN myTable as m1 on m1.prd = m0.prd and m1.bin=1 LEFT JOIN myTable as m2 on m2.prd = m0.prd and m2.bin=2 LEFT JOIN myTable as m3 on m3.prd = m0.prd and m3.bin=3 LEFT JOIN myTable as m4 on m4.prd = m0.prd and m4.bin=4 LEFT JOIN myTable as m5 on m5.prd = m0.prd and m5.bin=5; 217|2.1|1.3|7.1|6.5|7.6 217|2.1|1.3|7.1|6.5|7.7 298|1.1|6.2|8.1|8.4|1.1 298|1.1|6.2|8.1|8.4|7.3 298|3.1|6.2|8.1|8.4|1.1 298|3.1|6.2|8.1|8.4|7.3 David ________________________________ From: Rick Guizawa <guizaw...@gmail.com> To: sqlite-users@sqlite.org Sent: Sunday, January 27, 2013 9:33 PM Subject: [sqlite] unique combination of concatenated column query Hi, All. Could you, please, help me with sql query ? For example, if I have a table1 like the following (fields/values separated by commas): component, "bin", "prd" 2.1 , "1", "217" 6.5 , "4", "217" 7.1 ,"3", "217" 7.6 ,"5", "217" 7.7 ,"5", "217" 1.3 ,"2", "217" 1.1 ,"1", "298" 3.1 ,"1", "298" 6.2 ,"2", "298" 7.3 ,"5", "298" 8.1 ,"3", "298" 8.4 ,"4", "298" 1.1 ,"5", "298" and I want to produce a table2 below: prd , combo 217 , 2.1|1.3|7.1|6.5|7.6 217 , 2.1|1.3|7.1|6.5|7.7 298 , 1.1|6.2|8.1|8.4|1.1 298 , 1.1|6.2|8.1|8.4|7.3 298 , 3.1|6.2|8.1|8.4|1.1 298 , 3.1|6.2|8.1|8.4|7.3 whereas column 'combo' contains all possible combinations of components in table1 written in ascending order of table1 'bin' values. Thank you in advance as your help is very much appreciated. Cheers, ric _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
