SQL Query

  1. 3 months ago

    Jeff T

    Jan 8 Pre-Release Testers Midlands of England, Europe

    Off topic, but just in case anyone can help..
    I dont know what such a requirement would be called, so Im struggling to search online for a meaningful answer

    I have several tables, with one-to-many joins

    Person1
    Person2 etc

    Person1/Attribute 2
    Person1/Attribute 3
    Person2/Some attribute

    person1/Nickname1
    person1/nickname2
    person2/nickname5

    These aren't real columns or fields, but I have a requirement from a customer who doesnt understand the nature of joins, cartesian joins and so on, who feels that,
    despite Person1 having several attributes AND several nicknames
    They want a dataset with one row per person

    If I join these together into a single row of data, obviously I get 4 rows for Person1 .. as many rows as Attribute * Nickname in the contrived example above.

    Max() isnt acceptable.

    So what I am wondering is :
    Is there a SQL method of taking

    Person1/Attribute 2
    Person1/Attribute 3

    And getting

    Person1/ Attribute 2 , Attribute 3

    in order that I can put all Attributes (however many there are) into a single column.
    Like a pivot table, but without turning rows into many columns, just one

    drop table xyz;
    
    create table xyz (person text,attr text);
    
    insert into xyz values("Dave","abc");
    insert into xyz values("Dave","123");
    insert into xyz values("Fred","xyz");
    insert into xyz values("Fred","456");
    
    select person,group_concat(attr,',') as attribute
      FROM xyz
    group by person;

    results in

    Dave abc,123
    Fred xyz,456

  2. Kem T

    Jan 8 Pre-Release Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    What database?

  3. Dave S

    Jan 8 San Diego, California USA

    look at GROUP_CONCAT

  4. Rick A

    Jan 8 Pre-Release Testers (Brazil. UTC-3:00)
    Edited 3 months ago

    Maybe you need a pre-prossessing algorithm creating a temporary table with all the necessary data transposed as you need, do your processing joining that table, and drop it after. This is specially needed when using things like SQLITE that does not contain pivotal/transposing instructions.

  5. Norman P

    Jan 8 Pre-Release Testers, Xojo Pro outside drowning sorrows

    google for "sqlite convert rows to columns sql query" and read as many of the hundreds of hits that come up :P

  6. Dave S

    Jan 8 Answer San Diego, California USA
    Edited 3 months ago
    drop table xyz;
    
    create table xyz (person text,attr text);
    
    insert into xyz values("Dave","abc");
    insert into xyz values("Dave","123");
    insert into xyz values("Fred","xyz");
    insert into xyz values("Fred","456");
    
    select person,group_concat(attr,',') as attribute
      FROM xyz
    group by person;

    results in

    Dave abc,123
    Fred xyz,456

  7. Kem T

    Jan 8 Pre-Release Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    PostgreSQL has something similar.

  8. Rick A

    Jan 8 Pre-Release Testers (Brazil. UTC-3:00)

    For SQLITE, Dave's answer can be an option if an arbitrarily joint data as one string in one column is enough. I see that group_concat exists since SQLITE 3.5.4.

  9. Kem T

    Jan 8 Pre-Release Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    From Jeff's question:

    @Jeff T in order that I can put all Attributes (however many there are) into a single column.

  10. Rick A

    Jan 8 Pre-Release Testers (Brazil. UTC-3:00)

    @Kem T From Jeff's question

    Yep. I had just the:

    @Jeff T Like a pivot table

    part stuck in my head.

  11. Jeff T

    Jan 8 Pre-Release Testers Midlands of England, Europe

    google for "sqlite convert rows to columns sql query"

    Columns plural is the problem.
    I need rows to a single column.

    Its an Oracle database. I'll see if it has a group_concat.
    Thanks for the pointer , Dave

  12. Dave S

    Jan 8 San Diego, California USA

    @Jeff T Its an Oracle database. I'll see if it has a group_concat.

    It does, but its called something different.... I used to use it in Oracle alot, but it has been years

  13. Jeff T

    Jan 8 Pre-Release Testers Midlands of England, Europe

    Actually, I think we have a winner:

    GROUP_CONCAT(expr)

    This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
    [ORDER BY {unsigned_integer | col_name | expr}
    [ASC | DESC] [,col_name ...]]
    [SEPARATOR str_val])

  14. Dave S

    Jan 8 San Diego, California USA

    LOL.... it was the same syntax even?

  15. Kem T

    Jan 8 Pre-Release Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    I had just found this which suggests LISTAGG:

    https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle

    Just FYI, in case it helps.

  16. Jean-Yves P

    Jan 8 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    and postgres is array_agg !

  17. Tobias B

    Jan 9 Pre-Release Testers, Xojo Pro Bern, Switzerland

    For an overview on the syntax and implementation variants among several DBMS concerning the listagg feature standardised in SQL:2016, see https://modern-sql.com/feature/listagg

or Sign Up to reply!