Rotate SQL SELECT list of results with one column on one row

Question

How can I make a statement:

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

return

value1, value2, value3 (where value1 is for USA, value2 for BRA, value3 for CHN)

The best I've got so far is a single column with all the values ​​that appear as they are pulled out of the database.

Answer 1

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

Answer 2

You are probably looking for the GROUP_CONCAT () function:

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

Answer 3

sql pivot. .

Answer 4

. Python :

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)

Answer 5

I believe you are looking for a table twist. I answered a similar question here , however, I am not familiar with SQLLite, so it may be similar.

Answer 6

Sort of:

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'

Answer 7

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'
;

The output will be:

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

Answer 8

   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 data pass (one query), and you combine three rows, in other words nothing.

Answer 9

This should work (verified 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

it is right.