SQL Query

General
1

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

2

What database?

3

look at GROUP_CONCAT

4

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

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

6 
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

7

PostgreSQL has something similar.

8

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

From Jeff’s question:

[quote=470593:@Jeff Tullin]in order that I can put all Attributes (however many there are) into a single column.
[/quote]

10

Yep. I had just the: [quote=470593:@Jeff Tullin]Like a pivot table[/quote] part stuck in my head.

11

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

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

13

Actually, I think we have a winner:

GROUP_CONCAT(expr)

[quote]
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])[/quote]

14

LOL… it was the same syntax even?

15

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

and postgres is array_agg !

17

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