Questions tagged [common-table-expression]
A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE or DELETE statement.
common-table-expression
4,266
questions
0
votes
0
answers
44
views
Understanding multiple CTE filtering when used in a view
This is the same question as Understanding multiple CTE filtering when used in a view. inPostgreSQL, but for SQL Server and with reproduction code.
An ex colleague of mine has written many highly ...
- 633
0
votes
0
answers
29
views
Is there a faster way to concatenate strings in a column other than looping? [duplicate]
I know that if I have a single column table (call it tbl) with values like this:
col
---
A
B
C
and I want to get A,B,C as a single string, I can do this:
DECLARE @s varchar(20)
SELECT @s = @s + col + ...
- 75
1
vote
2
answers
48
views
Is there a way to retrieve a parent child tree from a single table, with multiple roots and a child_order column?
I have a table that looks like this:
CREATE TABLE tbl
(
id int,
parent_id int,
child_order int,
name varchar(20)
)
Take this as a sample dataset:
1 NULL 0 BtVS
2 NULL 1 ...
- 75
0
votes
0
answers
59
views
Understanding multiple CTE filtering when used in a view. inPostgreSQL
An ex colleague of mine has written many highly complex nested-view views using multiple CTEs.
The end views are very fast.
It seems to me that his end views filter the initial CTEs, making the rest ...
- 633
0
votes
0
answers
41
views
Combining the "WITH clause" and "MERGE statement" directly into one SELECT statement
Is it possible to get a result if I combine the WITH clause and MERGE statement directly into one SELECT statement? Combining WITH clause with INSERT, UPDATE and DELETE works but MERGE does not work ...
- 350
0
votes
0
answers
83
views
Understanding performance lag on a simple CTE
I am trying to calculate the carryover of PTO hours from one year to the next. In order to do this, I need to know the previous year's "remaining balance", which requires knowing the ...
- 437
0
votes
1
answer
36
views
Combine parent id and all child ids in an array in a single row using recursive cte
I have a table pr with two columns:
stg source id, parent id
125, 124
126, 125
127, 125
128, 127
what I want a final result as:
parent id, stg_source_ids
124, [125, 126, 127, 128]
as my final row ...
0
votes
1
answer
55
views
How to insert missing rows into a table based upon a group by?
I'm trying to figure out how to loop through a table and insert missing rows with '0' or null scores based upon the name. For example, I have a table with these values:
Stage
ID
Name
Score
stage 1
...
0
votes
0
answers
36
views
h2db: can I use parameterized WITH clauses (CTEs) in subqueries
I'm using h2db 2.2.224 as Test-DB in Spring Boot 3.2.5, running a JPA native query.
I have a WITH clause (CTE) that uses a query parameter. This WITH clause does not work in a subquery against h2db. ...
- 13
0
votes
0
answers
42
views
Is there a way to optimize this query in Tsql?
WITH CTE AS
(
WITH flattened_data AS
(
SELECT
DISTINCT
MAX(CASE WHEN (entry.value:Id::STRING)='123' THEN entry.value:Answer:text::STRING END) AS VD,
MAX(CASE WHEN (...
0
votes
3
answers
69
views
How to retrieve Common Table Expressions (CTEs) as a R list?
Context
I am connected to a PostgreSQL database and I use dplyr + dbplyr to query it. One of the query is quite long and complex and uses multiple CTEs.
Problem
I need to use these CTEs to make some ...
- 2,907
0
votes
1
answer
61
views
SQLAlchemy: map CTE/group_by results on ORM entity
Problem:
I have a complex query in which, at a certain point, in case a flag is set, I eventually need to group_by() and e.g. sum() and group_concat(). My problem is that after doing that I fail to ...
0
votes
1
answer
29
views
SQL assign a value from a column in table A until all the values of a column in table B are populated
I have a table box as depicted below.
box_name
---------
box1
box2
box3
box4
box5
box6
box7
box8
box9
box10
And another table color as shown below
color
------
red
blue
green
My objective is to ...
- 3,342
0
votes
0
answers
57
views
MySql recursive query using pivot table (many-to-many relashionship)
I'm having a huge performance issue when I run recursive query. Here is my DB Structure:
users:
id(index)
name
1
A
2
B
3
C
4
D
5
E
user_user
user_id(index)
parent_id(index)
2
1
3
2
4
2
5
4
...
- 38
1
vote
0
answers
21
views
Does it make a difference if a CTE comes before or after an insert?
insert statements in postgres can use CTEs ("with" expressions). These can come either before or after the insert itself. Is there any difference between one style vs the other?
For example, ...
- 1,044