0

This seems like a simple operation so apologies if I've missed an existing answer.

What I need to do is extract single record (a client) from a database of clients (in Spatialite) and present the query results as a transposed table.

So, to simplify, the client database would have fields; name, address, phone number, etc. for a large number of clients (one row each). I want a query which will return a table as below for a particular client I pick.

field,value

name, {value in 'name' field for selected client}

address, {value in 'address' field for selected client}

phone number, {value in 'phone number' field for selected client}

etc.

Hope that makes sense.

Improve this question
5
  • how do you want to pick the client? by location, id, manual selection? ...from a first glance this sounds like e.g. a simple SELECT * FROM <clients> WHERE id = <some_id>; is what you're looking for?
    – geozelot
    Oct 23, 2018 at 8:48
  • @ThingumaBob Ideally, I'd like to do it by the project variable of the current project '@project_title', but I expect that's a bit too much to ask, so I'd be happy to do it manually, but easiest if it is by the 'name' column.
    – user39790
    Oct 23, 2018 at 8:51
  • @ThingumaBob Your suggestion extracts the record for me, it's transposing it I'm still having trouble with. I want the resulting table to be the other way round - columns become rows.
    – user39790
    Oct 23, 2018 at 9:05
  • ah...sorry, didn't read carefully. I fear transposing/pivoting is not natively supported in SQLite, AFAIK; manually restructuring (like here) might just be the only way to go...maybe better ask this at DBA SE (also, since, strictly speaking, there's no GIS component in your question).
    – geozelot
    Oct 23, 2018 at 9:15
  • @ThingumaBob OK, thanks. There might be something I can get out of the code in the link you provided. I suppose you're right right about the lack of GIS component, It's only that I wanted to be able to do the whole thing from within Qgis, so was looking for an answer from people who use that software, but if it's just an SQLite issue then you're right, I might be better off asking elsewhere.
    – user39790
    Oct 23, 2018 at 9:21

1 Answer 1

Reset to default
1

Just for anyone else trying to do this within QGIS, I've found a way.

  1. Create a data only layer with a single column 'FIELD'. This should be populated with all the fields (their exact names) from your target layer that you want transposed as records.

  2. Load it into your project.

  3. Use field calculator to create a new column 'Results' and populate it with the expression dbvalue( '{target layer}', "FIELD" ,'{the field containing the id of the record you want}', '{the id of the record you want}')

You end up with a data layer containing the record you're after transposed.

This requires the ref Functions plugin.

Improve this answer
1
  • +1 - neat workaround.
    – geozelot
    Oct 23, 2018 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.