SQL Question: Exporting to tab-delimited

Databases
1

Consider two tables:
people
answers

The people table contains name, email etc for a person in each row.
The answers table contains answers for a person for questions. One answer per row. (There are at most five questions/answers.)

What I want to do is write out a tab-delimited file (so someone can play around with the data in Excel) where each line in the file has the person data (not a problem) and all the answers for that person.

The only way I have come up with to do this is while writing a line to the file do a query to get that person’s answers, assemble them into a string and stick them at the end of the line.

Anyone have any better ideas.

2

SQLite?

Normally you’d query with a join to get all the person/answer combinations. Or did I misunderstand the question?

3

I just did a query. I get something like

name1, answer1
name1, answer2
name1, answer3
name2, answer1
name2, answer2
name2, answer3

What I want is:
name1,answer1,answer2,answer3
name2,answer1,answer2,answer3

4

How about something like this?

select 
  name,
  '"' || group_concat( answer, '", "' ) || '"'
from
  person as p
  join answer as a on a.person_id = p.id
group by 1
;
5

I tried this. Problem: some people don’t have all the answers.

Thanks for the help.

Done for now.

6

Use LEFT JOIN to get all the names even if there are no answers.

7

If this was a SQL Server or Oracle question, the answer would be in a SQL command called PIVOT.
SQLite doesn’t have that command.

You need to do this with a set of LEFT OUTER joins, and making use of the COALESCE(,) function.
This ensures that if someone has no answers, or a partial set of answers, that you always get some response.

For example, if there is an answer row where the Question is ‘CITY’, you need to create one query that returns CITY for everyone.

Select PERSON_ID, COALESCE(Answer, " ") as CITY from ANSWERS;

If you have 3 people, that might return

1, “Chicago”
3, “Boston”

note that person 2 has no answer at all.
Save the query / view as qryCITY

Now, you repeat that for TOWN, and all the other answer fields

========

Finally, you wrap them all up using a combination query

SELECT a.PERSON_ID,
a.SURNAME,
a.FORENAME,
COALESCE(b.CITY, " “) CITY,
COALESCE(c.TOWN,” ") TOWN
from
PEOPLE a LEFT OUTER JOIN qryCITY b
on a.PERSON_ID = b.PERSON_ID
LEFT OUTER JOIN qryTOWN c
on a.PERSON_ID = c.PERSON_ID;

Now, all of that is typed straight from my head to the page, as I don’t have a SQL test bed set up at the moment,
but hopefully that will get you where you need to go.

2 Likes
8

It looks like you want to ‘pivot’ your query. (Jeff confirm this information above, I typed this yesterday)
There is an extension for sqlite: GitHub - jakethaw/pivot_vtab: SQLite3 pivot virtual table
There are tools that offer pivot like: PIVOT Statement - DuckDB
Maybe MBS offers the pivot extension.
You can open a feature request to add the extension to Xojo.