Turn a list of single-column SQL SELECT results into a single line

How can I make the statement:

SELECT column1 FROM db WHERE country in ('USA','BRA','CHN') and commodity='pork'

return

value1, value2, value3 (where value1 is for USA, val开发者_JS百科ue2 for BRA, value3 for CHN)

The best I got so far was a single column with all the values appearing as they are pulled from the database.

---

You're probably looking for the GROUP_CONCAT() function:

http://www.sqlite.org/lang_aggfunc.html

---

You can do the following:

Select a.column1,b.column2,c.column3 from
(SELECT column1 FROM db WHERE country ='USA' and commodity='pork') as a,
(SELECT column1 FROM db WHERE country ='BRA' and commodity='pork') as b,
(SELECT column1 FROM db WHERE country ='CHN' and commodity='pork') as c

---

Use sql pivot. This will help you.

---

This is a presentation issue. Easier to let Python handle it:

import sqlite3
conn = sqlite3.connect('/path/to/your.db')
curs = conn.cursor()
results = curs.execute("""SELECT column1 
                            FROM db 
                           WHERE country in ('USA','BRA','CHN') 
                             AND commodity='pork';""").fetchall()
', '.join(str(result[0]) for result in results)

---

I believe you are looking for Table Rotation. I answered a similar question here However I am not familiar with SQLLite so it might be similar.

---

Something like:

SELECT column1 as value1 FROM db WHERE country='USA' and commodity='pork'
UNION
SELECT column1 as value2 FROM db WHERE country='BRA' and commodity='pork'
UNION
SELECT column1 as value3 FROM db WHERE country='CHN' and commodity='pork'

---

   select max(case country
                 when 'USA' then column1
                 else null
               end) USA,
           max(case country
                 when 'BRA' then column1
                 else null
               end) BRA,
           max(case country
                 when 'CHN' then column1
                 else null
               end) CHN
    FROM db
    WHERE country in ('USA','BRA','CHN')
     and commodity='pork'

One single pass over the data (single query), and you aggregate three rows, in other words nothing.

---

Do you want something like this:

select 
  base.commodity
,USA_db.column1 as Commodity_USA
,BRA_db.column1 as Commodity_BRA
,CHN_db.column1 as Commodity_CHA
from (select 'pork' as commodity) as base
left join db as USA_db on base.commodity = USA_db.commodity and USA_db.country = 'USA'
left join db as BRA_db on base.commodity = BRA_db.commodity and BRA_db.country = 'BRA'
left join db as CHN_db on base.commodity = CHN_db.commodity and CHN_db.country = 'USA'
;

Output will be:

commodity   Commodity_USA  Commodity_BRA  Commodity_CHA
----------  -------------  -------------  -------------
pork        USA-pork       BRA-pork       USA-pork

---

This should work (Tested in mysql)

SELECT GROUP_CONCAT(column1) FROM db WHERE country in ('USA','BRA','CHN') and commodity='pork'

my testing is this (in mysql)

SELECT GROUP_CONCAT( uid )  FROM pages where title in ('Home','Footer','home')

result:

1,3,10

this is correct..