I have the following table: I want tto create new table which will show the unique crop names, count of the crops appearence and then list of all the growers that grow this crop,so the result table should look like this: I manage to create table that shows the crops and the total count without the growers names: My question
Tag: string-aggregation
How to use wm_concat one a column that already exists in the query?
So… I am currently using Oracle 11.1g and I need to create a query that uses the ID and CusCODE from Table_with_value and checks Table_with_status using the ID to find active CO_status but on different CusCODE. This is what I have so far – obviously does not work as it should unless CusCODE and ID are provided manually: Table_with_value: Table_with_status:
Build a report with SQL with combining columns and rows?
Here is my table: Table Purchase Report PURC_ID CUS_ID Product QTY Date 1 John Leg_01 2 2021-04-09 2 John Head_01 1 2021-04-09 3 Dawn Head_01 1 2021-04-09 4 Dawn Shoulder_01 2 2021-04-09 5 Dawn Leg_01 1 2021-04-09 6 Keith Leg_01 2 2021-04-09 I would like to build the report as follow: Table 4: (PURC table will combine with other columns.
How to pivot rows into a single comma separated string
Important update: When I try to use the suggested string_agg method I get this error – Specified types or functions (one per INFO message) not supported on Redshift tables. Original question I have a query but I’m struggling to “pivot” multiple rows into a single column of strings. I have a member and a category table and each member can
How to concat all the entries of one column [SQL]
As the title states, with the following SQL query: I get the following result: Question: how to modify the query so that all the names are to be displayed in a row, so that they can be later used an array. What I tried: FOR XML, STUFF – doesn’t work Expected result: Answer If you are using SQL Server you
SQL Concatenate all values in column B that have same value in column A for all values in column A
I am running PostgreSQL 12.4. I have a relatively large table like the following where column 1 and 2 are both of character varying type: I would like to create something like the following: Is there an easy way to do this? Answer You can use string_agg: You can find more info here.
One-to-Many SQL SELECT concatenated into single row
I’m using Postgres and I have the following schemes. Orders Comments So, in this case, an order can have many comments. I need to iterate over the orders and get something like this: I tried to use LEFT JOIN but it didn’t work it returns this: Answer You are almost there – you just need aggregation: I would strongly recommend
SQL Server Concatenate three different columns into a Comma-Separated without repeated values
The next table is a simplification of my problem in SQL Server: I want to get a group with the columns concatenated by comma without repeated values. I tried to use STRING_AGG() but it returns: This is the query I have done: I would like the next result: Thank you! Answer Without using window functions. The union might slow things
Get unique values using STRING_AGG in SQL Server
The following query returns the results shown below: SELECT ProjectID, newID.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],’;’) AS newID WHERE newID….
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
I’m trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that’s life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one …