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
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
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.
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;
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.
[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:
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