Stack Overflow
Stack Overflow

Questions tagged [lateral-join]

LATERAL Derived Table

A LATERAL derived table is a subquery in the FROM clause that can reference preceding JOIN-ed tables (in the same FROM clause.)

Example:

SELECT ... FROM t1 JOIN LATERAL (SELECT * FROM t2 WHERE t2.cx = t1.cy) tl ON ...
212 questions
0
votes
2 answers

Create a PostgreSQL view from a unique list of exploded data

When I make this request: SELECT mycol FROM mytable WHERE mycol IS NOT NULL; I get results like this: foo / bar / hello foo / hello foo / bar Each line has values separated with / (space, slash, space). How to create a view with a list of unique…
DevonDahon
  • 7,460
  • 6
  • 69
  • 114
0
votes
1 answer

Postgres, split single row into multiple rows

I've got a table with columns like: +-------+------+-------+-------+-------+-------+ | age1 | val1 | age2 | val2 | age3 | val3 | +-------+------+-------+-------+-------+-------+ | 30-40 | 34.5 | 41-50 | 32.01 | 51-60 | 29.13…
JP Silvashy
  • 46,977
  • 48
  • 149
  • 227
0
votes
3 answers

Insert 2 select in same insert SQL

i have a problem, i would like use 2 select to insert in new table but i don't know if it's possible... my code : insert INTO attributs_libres_lignes VALUES ( SELECT LIGN_PKID, "INSERT HERE" from ATTRIBUTS_LIGNES a, "HERE THE NUMBER OF COLUMN OF…
Prometheus
  • 3
  • 6
0
votes
1 answer

SQL Server Datediff for an entire table

I have a table called dbo.employees, I run some queries and I get the below result. empId | timeIn | timeOut | timeDiff ------+----------+----------+------------ 1001 | 03:49:54 | 15:43:42 | 1017 | 06:27:10 | 13:47:40 | 1005 | 05:49:50 |…
Joshua Hateley
  • 13
  • 2
0
votes
1 answer

SQL filtering a text string by unique values

My PostgreSQL database includes a column which is a text string that takes unique but unpredictable values, like so: id var 1 "A", "B" 2 "B", "C" 3 "C", "A" 4 "eggs", "toast" 5 "eggs", "bacon" I need a SQL query which pulls…
mmyoung77
  • 1,343
  • 3
  • 14
  • 22
0
votes
1 answer

Rolling Sum for Last 12 Months in SQL

I'm trying to get the rolling sum for the past 12 months (Oct 2019-Sept 2020, etc.)> So far, I figured out to get the current year total (which I also want), however, I'm stuck on a legit 12 month rolling sum. SELECT DATEADD(MONTH,…
user14316330
  • 61
  • 1
  • 6
0
votes
2 answers

sql how to convert multi select field to rows with totals

I have a table that has a field where the contents are a concatenated list of selections from a multi-select form. I would like to convert the data in this field into in another table where each row has the text of the selection and a count the…
scimeup
  • 1
  • 2
0
votes
2 answers

Unpivoting two separate columns

I am trying to Unpivot multiple columns from a table. So far I have tried to just use a standard Unpivot and it is successful for the first part, but not the second. I would like to have both columns Unpivote. I have attached a picture of what I am…
Stormfrazier
  • 1
  • 1
0
votes
2 answers

SQL Conditional on SELECT Subquery value

I want to apply a condition check on my select subquery. How can I do it optimally? Here's the initial query: SELECT table1.column1, (SELECT min(table2.column1) FROM table2 WHERE table2.table1Id = table1.id) as subResult FROM table1 WHERE…
George
  • 2,820
  • 4
  • 29
  • 56
0
votes
3 answers

logic to create more rows in sql

I have a table1 that I wanted to transform into the expected table. Expected table logic for columns: cal: comes from cal of table1. ID comes from the ID of table1. code: this is populated with lp or fp depending upon if we have a value in f_a then…
KSp
  • 1,199
  • 1
  • 11
  • 29
0
votes
2 answers

Is there a function in PostgreSQL that counts string match across columns (row-wise)

I want to overwrite a number based on a few conditions. Intended overwrite: If a string (in the example I use is just a letter) occurs across 3 columns at least 2 times and the numerical column is more than some number, overwrite the numerical value…
eyei
  • 402
  • 4
  • 12
0
votes
2 answers

How to convert a query to use joins?

I want to write a query that uses 'joins concepts' to return, for each order, the number of days that passed since the same customer's previous order. I am using the AdventureWorks2017 database, table Sales.SalesOrderHeader I tried the following…
Kartheek
  • 1
  • 4
0
votes
2 answers

How to expand each row of a table into a variable number of rows in Postgresql

I have a table that defines objects with a given number of iterations, let's say it's a game with a variable number of rounds. # select * from game; game_id | num_rounds --------+----------- A | 2 B | 3 C | 1 I'd like to create a…
Rems
  • 4,837
  • 3
  • 27
  • 24
0
votes
2 answers

SQL Using specific input find corresponding columns and create new summary table

Data is a flat normalised table: |ID | Product selected | Product Code 1 | Product Code 2 | Product Code 3 | Cost of Product 1 | Cost of Product 2 | Cost of Product 3 | Rate of Product 1 | Rate of Product 2 | Rate of Product 3…
Laurie7
  • 1
0
votes
1 answer

Retrieve number of replies to a comment

I have the following PostgreSQL table to store comments on different posts: Comment table: commentID | parentID | postID | content | author | created I want now to retrieve with a single Query, given a postID, all comments to the post AND the number…
Konstantin
  • 67
  • 6
1 2 3
…
14 15