ttist25
asked on
Append the distinct values from a field to the end of a row
Hello,
I'm working in RedShift (PostGreSQL) and need to append distinct values for a field in one table to the end of another table.
Table1 has 2 fields;
T1.ID,
T1.Date
Table 2 has 3 fields:
T2.ID,
T2.Date,
T2.Items
T1.ID + T1.Date represent a single row in T1.
In Table2, T2.ID, and T2.Date appear in multiple rows given the number of distinct values in T2.Items.
The task is this. Joining T1 and T2 on T1.ID = T2.ID and T1.Date = T2.Date append the distinct values in T2.Items.
And to make it even more fun, the max number of distinct values in T2.Items for each ID+Date is not known.
Any ideas on a fairly graceful way to do this?
TIA!
I'm working in RedShift (PostGreSQL) and need to append distinct values for a field in one table to the end of another table.
Table1 has 2 fields;
T1.ID,
T1.Date
Table 2 has 3 fields:
T2.ID,
T2.Date,
T2.Items
T1.ID + T1.Date represent a single row in T1.
In Table2, T2.ID, and T2.Date appear in multiple rows given the number of distinct values in T2.Items.
The task is this. Joining T1 and T2 on T1.ID = T2.ID and T1.Date = T2.Date append the distinct values in T2.Items.
And to make it even more fun, the max number of distinct values in T2.Items for each ID+Date is not known.
Any ideas on a fairly graceful way to do this?
TIA!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks pcelba!
I ended up using listagg(T2.Items, ',' ) to get the distinct items in a field of comma separated values. I still need to break out the values into separate columns (e.g., item1, item2, item3, etc.) but I'm going to do that in Python or something cuz I can't figure out a SQL way.
Thanks again for your help!
I ended up using listagg(T2.Items, ',' ) to get the distinct items in a field of comma separated values. I still need to break out the values into separate columns (e.g., item1, item2, item3, etc.) but I'm going to do that in Python or something cuz I can't figure out a SQL way.
Thanks again for your help!
If you wanted individual columns there should be no need to create a CSV then split it back out.
Look at the old way to do a PIVOT before most databases created that function:
https://chartio.com/resources/tutorials/how-to-pivot-table-with-redshift-or-postgres/
ASKER
Thanks slightwv. I had been looking at PIVOT for this but I couldn't work it out. If I could get it to work I think it would be a much less clunky way of doing it.
I opened a new question with some DDL and a SQL Fiddle here https://www.experts-exchange.com/questions/29172329/PostgreSQL-Redshift-Append-Pivot-Distinct-Values.html
Thanks again for the help.
I opened a new question with some DDL and a SQL Fiddle here https://www.experts-exchange.com/questions/29172329/PostgreSQL-Redshift-Append-Pivot-Distinct-Values.html
Thanks again for the help.
Saw the new question. Will try to get some time today to look at it.
Please post sample data and expected results.