395 questions
-1
votes
1
answer
89
views
What is the correct LISTAGG command for the equivalent STRING_AGG [duplicate]
We recently migrated the data from SAP HANA to Oracle. The database is Oracle 19c.
I need to know the corresponding Oracle 19c command for the below query. The below query is utilizing STRING_AGG. ...
- 67
0
votes
1
answer
153
views
Multiple STRING_AGG in one query
I have a dataset that looks like this:
Dummy table
The desired view I'd like is:
enter image description here
I have tried the STRING_AGG function, which works, but for only one column. See below for ...
1
vote
1
answer
280
views
Cannot perform an aggregate function on an expression containing an aggregate or a subquery with STRING_AGG
I am trying to query information from 2 tables. The PrimaryTable I simply want to return all columns from all rows in that table. The SupportTable I want to just get all of the ID's from that table ...
- 1,108
1
vote
1
answer
129
views
Using string_agg to create distinct groups by row
Consider 'Table 1' below:
id
x
1
A
1
B
1
C
2
B
2
C
3
A
I want to query Table 1 to produce the following:
id
x
grps
1
A
B, C
1
B
A, C
1
C
A, B
2
B
C
2
C
B
3
A
Using string_agg and grouping by id ...
- 1,134
1
vote
1
answer
3k
views
STRING_AGG with OVER() clause in SQL Server
Is there any equivalent of Postgres: STRING_AGG with OVER() or Oracle LISTAGG with OVER() in SQL Server? I would like to concatenate text values without grouping it, so I need STRING_AGG in analytical ...
- 137
0
votes
1
answer
110
views
Table column aggregation results in the same value duplicating multiple times in one cell
I have a set of tables aimed at representing a profile storage system. I have created a view that should display all the important parts of this profile, that being things like the name, email, number ...
- 25
0
votes
1
answer
48
views
Subquery and group by : Validation failed: mismatched input 'FROM' expecting <EOF>
I want to calculate the cost by multiplying a price with the quantity of my equipment.
My data are like that :
idnt_eqpm
prx_tot_net
prix_pump_ht
10954847
10
8
And I want this data :
idnt_eqpm
...
- 3
0
votes
1
answer
84
views
Oracle format String output from Aggregate Collect function
Below is my working Oracle query-
-- User-Defined TYPE
create or replace TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
-- This works
select emp.DEPT_NAME,
CAST(
COLLECT(emp.EMP_ID ...
0
votes
1
answer
96
views
Oracle Collect function on Multiple Columns gives Error
Reference Tutorial-
oracle-developer.net
Below is my working Oracle query-
-- User-Defined TYPE
create or replace TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
-- User-Defined FUNCTION
CREATE FUNCTION ...
0
votes
1
answer
61
views
ORACLE SQL Excluding results within Select (“Result of string concatenation is too long)
For the last couple of days my head is spinning around this issue and I am really hoping you can help me with this. So simplifying the issue. This is the table I already have:
ID
Message
Time
Zone
1
A
...
2
votes
1
answer
3k
views
Snowflake - LISTAGG with LIMIT
Snowflake allows to concatenate string across multiple rows by using aggregate/windowed LISTAGG function. Sometimes we just need first few occurrences per group. Similar to GROUP_CONCAT with LIMIT:
...
- 181k
2
votes
3
answers
199
views
How to optimise a query with multiple subqueries for string aggregation?
This is my query:
SELECT
wp.WorkplanID,
STUFF((SELECT ', ' + ISNULL(ul.FirstName + ' ', '') + ISNULL(ul.LastName, '')
FROM UserLogin ul
INNER JOIN Vendors v ON ul....
0
votes
4
answers
113
views
How do I return identical values between two string aggregated columns in SQL?
I have 2 columns A & B in a SQL Server table that contain a string aggregated list of codes.
The codes in the lists are separated by a semicolon.
The string aggregated lists can take any length ...
- 3
-1
votes
1
answer
2k
views
SQL: Use STRING_AGG with condition
I have the following PostgreSQL query: (This is a Kata at codewars, you can find it at the following link: https://www.codewars.com/kata/64956edc8673b3491ce5ad2c/train/sql )
SELECT * FROM (
SELECT
...
- 1,496
-1
votes
3
answers
240
views
SQL concatenate row by row by date order with in group
I am looking for a way to concatenate the rows into a comma separated string.
Example:
I am looking for result as below where Result Seq column should concatenate the result column row by row values ...
- 141
-1
votes
1
answer
50
views
What's the best way to create a string of childIDs for each parentID in SQL, while excluding the current childID from the aggregate
Data
DROP TABLE IF EXISTS cats;
CREATE TABLE cats
(
litterID int NOT NULL,
catID int NOT null, --row uniquifier
catFirst varchar(30),
catLast varchar(30),
catSize int
);
INSERT ...
- 864
0
votes
0
answers
251
views
Question marks appearing in query result when I concatenate values from multiple rows [duplicate]
I am trying to concatenate values in a column when those values correspond to the same ID number. Here's an example of the data:
The query result I want is this:
Here is the code I tried to achieve ...
-1
votes
1
answer
92
views
How to find the column of a specific value in a table (SQL Server)
I have a table with more than 10,000 records. When executing the query, I get an error message
Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value 'balance' to ...
0
votes
1
answer
103
views
SQL - Pivot Column To Rows with Group By and Concat [duplicate]
I need to traspose a column into a row ( by concatenating it) with group by...
using : sql server 2019
Example
Car_ID
Car_Name
Owner
1
Ferrari
Marco
2
Jeep
Enrico
3
Mercedes
Matteo
1
Ferrari
Andrea
3
...
- 75
0
votes
2
answers
37
views
Is it possible to use three LISTAGGs and group by elements in row?
I have a situation where I would like to put three different columns, each which have multiple rows, into a single row.
I am using this code, which works to give me all of the first columns, then the ...
- 71
1
vote
1
answer
128
views
How do I group by values, but only when they're consecutive?
I have the following table:
ID
Number
Size
7
1
1.5
7
2
1.5
8
1
1.625
8
2
1.03125
8
3
1.03125
8
4
1.03125
8
5
1.625
8
6
1
8
7
1.625
8
8
1.625
8
9
1.625
9
1
1
9
2
2
9
3
3
9
4
4
9
5
1
I would like to ...
- 345
0
votes
1
answer
148
views
How to concatenate multiple rows
I have an SQL Server script that concatenates multiple row based on unique ID. I need to be able to combine them into 1 row to avoid showing multiple lines for the same unique ID. The code below is ...
- 683
0
votes
1
answer
2k
views
Concatenate multiple strings ordered (PIVOT)
I'm trying to concatenate the names of multiple Employees (ordered) based on their hierarchical level, which ranges between 0 and 4, without duplicates. The issue itself is fairly easy to solve (...
- 11
0
votes
0
answers
48
views
Using "FOR XML PATH" in Databricks Spark SQL in sql syntax [duplicate]
I'm using spark version 3.2.1 on databricks (DBR 10.4 LTS), and I'm trying to convert sql server query into a databricks sql query that runs on a spark cluster using spark sql in sql syntax. However, ...
- 724
1
vote
2
answers
188
views
Data Preparation for Market Basket Analysis using Apriori Algorith using R
I am planning to run apriori algorithm on a dataset to find association among product purchase. The dataset looks like this
order_id <- c('AG-2011-2040','IN-2011-47883', 'HU-2011-1220','IT-2011-...
- 31
1
vote
1
answer
276
views
SQL Server 2019 - Ordering in sub select
In my database I have a Tracking Codes table as a child of a single Employment table. Along with other data, I am trying to return all of the Tracking Codes in a comma separated string so there will ...
- 69
1
vote
2
answers
122
views
Restrict string_agg order by in postgres
While working with postgres db, I came across a situation where I will have to display column names based on their ids stored in a table with comma separated. Here is a sample:
table1 name: labelprint
...
- 127
0
votes
0
answers
57
views
How to combine multiple rows into one, depening on an ID coming from a different table [duplicate]
I'm trying to add a number of values into one, depending on a value coming from multiple different tables. I keep getting a syntax error. So I have the following table:
OT_ID
CODE
OPTION
OPTION2
1
6T
...
- 25
3
votes
1
answer
121
views
How can I aggregate rows together according to a selected column using a pandas DataFrame
this is my first question in Stack Overflow.
I will water down the problem that I have at the moment. I am trying to clean a dataset for a User-based collaborative filtering recommendendation system.
...
- 33
0
votes
1
answer
369
views
Use STRING_AGG to get Values from 3 different Tables
I am trying to get comma-separated values from the following 3 tables:
Teams: Id
Players: Id, Name
TeamsPlayers: Id, TeamId, PlayerId
What I am trying to get is a result like this:
TeamId
PlayerNames
...
- 139
0
votes
0
answers
68
views
Merge rows if they contain a duplicate value in one of many columns
SQL Fiddle
I'd like a query that will merge rows that have if they have the same value in one of 3 columns.
If one of of the following columns matches the rows should merge.
customerid, externalid, ...
- 132
1
vote
2
answers
1k
views
Django queryset StringAgg on arrayfield
I have some data which includes sizes, much like the model below.
class Product(models.Model):
width = models.CharField()
height = models.CharField()
length = models.CharField()
Through ...
- 2,971
0
votes
0
answers
31
views
How to get the Current products grouping
The objective is to get the the current product grouping after product modification date
Tried string aggregation + replacing word for each product...never worked.
- 1
0
votes
0
answers
39
views
Grouping item recursively in sql
I have this table (test.mytable in the sql script below)
CREATE OR REPLACE test.mytable (item STRING(1), I_groupe STRING(1));
INSERT INTO test.mytable (item, I_groupe)
values
('A', '1'),
('B', '1'),
('...
- 3
0
votes
1
answer
259
views
How to concatenate sql table column with same (name) value in different columns [duplicate]
Let's suppose I have this table:
1|A|B|10
2|A|B|20
3|B|C|20
How to write a query to get this value:
1|A|B|10,20
3|B|C|20
0
votes
0
answers
261
views
how concatenate on heidisql [duplicate]
The issue is that I have each asset to have its tags, that is, number 6 to have 1,5 in a column, and asset 1 to have 1,3,5 PUTTING literally twice "asset_tag.tag_id" copies the same number ...
- 23
71
votes
2
answers
135k
views
STRING_AGG aggregation result exceeded the limit of 8000 bytes error
I need to combine texts by group. I found a function called STRING_AGG.
select c.id
, c.bereichsname
, STRING_AGG(j.oberbereich,',') oberBereiches
from stellenangebote_archiv as j
join bereiche as c ...
- 965
0
votes
2
answers
1k
views
SQL how to prevent duplicates in STRING_AGG when joining multiple tables
Here's the relational model for the database that I'm going to talk about (a show can have multiple actors and multiple genres): Relational model
(sorry, don't have enough reputation to insert an ...
- 95
0
votes
2
answers
1k
views
How to Concatenate Data from Different Rows into One in BigQuery
I want to concatenate my data from a particular column which is present in different rows.
The Data is something like this:
id
Name
1
Jack, John
2
John
3
John, Julie
4
Jack
5
Jack, Julie
I want the ...
- 23
0
votes
1
answer
387
views
How to aggregate/concate strings with no duplicates in SQL Server 2019
I am very new to SQL Server and it stucks in one task related to the aggregation of data. I tried to put all information on the screen attached. Is there anyone that could support it? My current code ...
- 1
0
votes
2
answers
2k
views
XMLAGG Throws Inconsistent Datatypes: Expected - got CLOB
In the first place, my query (actually subquery, continue on reading) contains a LISTAGG, but I need to replace it with XMLAGG when users are started to get result of string concatenation is too long ...
- 2,150
0
votes
0
answers
116
views
SQL Server 2016 string aggregation to select only not existing values
So, here is my SELECT query... it's working, but i want to get (with left join) only message types, which are NOT exists in CONFIG table:
SELECT PARTNER.ID AS [ID],
...
- 133
0
votes
1
answer
5k
views
Convert json to comma separated string in SQL Server [duplicate]
I have the following json passed into a prameter in SQL server 2016:
[
{
"item_name": "Settee"
},
{
...
- 10.2k
0
votes
0
answers
23
views
How to create group comma-separated integers in SQL [duplicate]
I have given SQL-query
SELECT * FROM (SELECT * FROM Customer
INNER JOIN Product
ON Customer.Ids = Product.CustomerId) as a
And here IS output that I have:
Customer_ids
name
gender
age
phone
ids
...
1
vote
1
answer
161
views
SQL Server: select from table with/without aggregate columns using groupby
I have the columns and rows for the table A,
tableid
featureid
col3
col4
coldatetime
1
1
AD
4
2022-06-22 09:00:00
2
2
BC
5
2022-06-22 09:00:00
3
1
AE
6
2022-06-22 10:00:00
4
3
BD
7
2022-06-22 11:00:00
...
2
votes
1
answer
976
views
Aggregate character string into vector in R
I have a data table test:
id
key
1
2365
1
2365
1
3709
2
6734
2
1908
2
4523
I want to aggregate unique key values by id into vector using data.table package.
Expected output:
id
key_array
1
"2365&...
- 485
1
vote
0
answers
206
views
PostreSQL, string_agg() join through association table
I have 2 tables linked together via 3rd association table:
TABLE NAME: lot
id | description | <other multiple columns> |
1 | descr_string_1 | ... |
2 | descr_string_2 | ...
- 161
0
votes
3
answers
5k
views
Sql server: limit string_agg result
I have the following query (showing for each customer the list of users):
select cu.customer_id , STRING_AGG(u.first_name + ' ' + u.last_name , ',') as users
from customer_user cu join user u on cu....
- 29
0
votes
1
answer
1k
views
Show column values as comma seperated in grafana
I have a table with 2 columns
organization_id | user_name
1 | abc
1 | xyz
2 | bhi
2 | ipq
2 | sko
3 | ask
...
Each organization could have any number of users ranging from 1 to 100, 2000 and so on.
I ...
- 13
0
votes
1
answer
2k
views
Multiple LISTAGG function in one SQL from different table
I am trying to get data from the different tables using the LISTAGG function in Oracle SQL developer. Able aggregate ID data, but unable to get corresponding ID's value from another table to another ...
