Questions tagged [string-aggregation]
This tag is intended for SQL questions where multiple string (character) values should be aggregated into a single value using GROUP BY. The equivalent of sum() - just for strings.
string-aggregation
392
questions
1
vote
1
answer
48
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 ...
- 1,022
-1
votes
1
answer
518
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 ...
- 117
0
votes
1
answer
94
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
32
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
43
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
55
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 ...
0
votes
1
answer
54
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
...
2
votes
1
answer
998
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:
...
- 171k
2
votes
3
answers
84
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
87
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
397
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,476
-1
votes
3
answers
127
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
46
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 ...
- 736
0
votes
0
answers
175
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
83
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 ...
