Query problems for view creation

[UCFSteve]

I have a set of data that looks like:

ID Location
----------------
1 1
1 2
1 5
1 6
1 9
2 2
2 5
2 3
2 9
3 5
3 8
3 10

I obtain this data doing a

SELECT DISTINCT ID, Location FROM tblBooks

Now I want to create a view, that will return me a list view of the past history of the books. I am using SQLite, so I cant use functions, and I am limited on the features available to me. Here is what I want my data to look like:


ID Locations
----------------
1 1,2,5,6,9
2 2,5,3,9
3 5,8,10

I would like to have the data from the location field concatenated with commas.

thank you for your input,

steve

 

[JarlH]

Sorry, but you can't do that using SQL. (At least not using standard SQL.)

You are trying to return an arbitrary number of Locations values for each row, but SQL requires a fixed number of columns to be returned.