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
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:
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.
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
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
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.
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
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
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….
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 …