sql - Group columns into one row, if they share the same other columns - Stack Overflow

admin管理员组

文章数量:1123268

I have a table in an SQLite3 database that looks like this:

col1	col2	col3	col4	col5	col6
fixed1	fixed1	fixed1	data11
fixed1	fixed1	fixed1		data12
fixed1	fixed1	fixed1			data13
fixed2	fixed2	fixed2	data21
fixed2	fixed2	fixed2		data22
fixed2	fixed2	fixed2			data23
fixed3	fixed3	fixed3	data31
fixed3	fixed3	fixed3		data32
fixed3	fixed3	fixed3			data33

I have a table in an SQLite3 database that looks like this:

col1	col2	col3	col4	col5	col6
fixed1	fixed1	fixed1	data11
fixed1	fixed1	fixed1		data12
fixed1	fixed1	fixed1			data13
fixed2	fixed2	fixed2	data21
fixed2	fixed2	fixed2		data22
fixed2	fixed2	fixed2			data23
fixed3	fixed3	fixed3	data31
fixed3	fixed3	fixed3		data32
fixed3	fixed3	fixed3			data33

I want to regroup col4-6 into a single row that shares the same col1-3 values:

col1	col2	col3	col4	col5	col6
fixed1	fixed1	fixed1	data11	data12	data13
fixed2	fixed2	fixed2	data21	data22	data23
fixed3	fixed3	fixed3	data31	data32	data33

I've tried the following:

select
    col1,
    col2,
    col3,
    max(col4),
    max(col5),
    max(col6),
from 
    foo
group by 
    col1, col2, col3

but to no avail. It returns:

col1	col2	col3	col4	col5	col6
fixed1	fixed1	fixed1
fixed2	fixed2	fixed2
fixed3	fixed3	fixed3

Additional info:

• In my real case, I have 6 "fixed" columns (that make up an index), and 5 "data" columns, with millions+ of rows.
• dataXX columns are a mix of int and bigint types.
• Empty cells in my table seem to be either NULL or empty strings (but not 0).
• If I use max(ifnull(-1,col4)) in my query, I get -1, not the value of col4.

• sql
• sqlite

Share Improve this question Follow edited 10 hours ago breversa asked 11 hours ago breversabreversa 10788 bronze badges 13

• 2 That should have worked – Bart McEndree Commented 11 hours ago
• 1 Your SQL is correct. Something else must be going on here. If you could show us your actual query and an excerpt of your actual data, we might be able to help you. – Olivier Jacot-Descombes Commented 11 hours ago
• 1 I am not able to reproduce your issue, like others mentioned in the comment.I am able to get correct results in output.see this fiddle dbfiddle.uk/AuhyH4dW – samhita Commented 11 hours ago
• 1 Blank cells are NULL or some sort of hidden character? – samhita Commented 11 hours ago
• 2 I now tried replacing NULL with blank space, your query still works :) dbfiddle.uk/jOL9JUkL – samhita Commented 11 hours ago

 |  Show 8 more comments

1 Answer 1

Sorted by: Reset to default 0

This is crazy, but if I replace max() by min(), it works… o_O

select
    col1,
    col2,
    col3,
    min(col4),
    min(col5),
    min(col6),
from 
    foo
group by 
    col1, col2, col3

本文标签： sqlGroup columns into one rowif they share the same other columnsStack Overflow