Create additional columns based on other column values in PostgreSQL
NickName:rshar Ask DateTime:2022-06-29T04:16:29

Create additional columns based on other column values in PostgreSQL

I have following data in a PostgreSQL table:

trial   start_date  end_date            
1       20_12_2001  20_01_2005

The expected output is below:

trial   start_date  end_date    Date[(start_end_date)]  marker_start_end
1       20_12_2001  20_01_2005  20_12_2001              start
1       20_12_2001  20_01_2005  20_01_2005              end

Is there a way to calculate the additional two columns (Date[(start_end_date)], marker_start_end) without join, but a CASE expression

Copyright Notice：Content Author:「rshar」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/72792660/create-additional-columns-based-on-other-column-values-in-postgresql

Answers
a_horse_with_no_name 2022-06-28T20:47:12

You can use a lateral join to turn two columns into two rows:\nselect *\nfrom the_table t\n cross join lateral (\n values (t.start_date, 'start'), (t.end_date, 'end')\n ) as x(start_end_date, marker); \n\nThe UNION ALL solution might be faster though.",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “Create additional columns based on other column values in PostgreSQL” related questions
Create additional columns based on other column values in PostgreSQL

I have following data in a PostgreSQL table: trial start_date end_date 1 20_12_2001 20_01_2005 The expected output is below: trial start_date end_date Date[(

Show Detail
Postgresql. Create table with column that concatenate values from other columns

I have create table script in MSSQL: CREATE TABLE [dbo].[Address]( [Id] [int] IDENTITY(1,1) NOT NULL, [Street] [nvarchar](100) NOT NULL, [BuildingNumber] [nvarchar](15) NULL, [

Show Detail
How to add a column in postgresql based on other columns

I would like to have a column called "newname" populate with a string with varying names based on a sql query in postgresql. IF fullstate='California' and county in (71 65) THEN newname='TEMECULA'...

Show Detail
SQL create new column based on two other columns

In PostgreSQL, I’m trying to create a new column based on values in two other columns. If early or late are in the Issues column, I want to display the time delta for every row of the delivery id. If

Show Detail
Generate new column based on values of other columns in R

I have the following table: | | Red | Green | Blue | Yellow | Brown | Purple | Black | | --- | --- | --- | --- | --- | --- | --- | --- | | Apple | A | B | D | D | C | F | E | | Pear | A | B | C | ...

Show Detail
how to split single column values into multiple columns based on other column values

I am trying to split single column values into multiple columns based on another column value, I could get it but I am unable to remove the additional null values I get table create table tbl1 (i...

Show Detail
Pandas create boolean column based on equality of other columns

Is there a simple way to dynamically (!!!) create a boolean column in a Dataframe, based on the values of the other columns, by checking if the values are equal? My DF: df = pd.DataFrame({"col...

Show Detail
Create column based on specific values in other columns

I am trying to create a new column based on specific values in several (four) other columns. Reprex: col1 <- c("a", "", "a") col2 <- c("", "b&qu

Show Detail
Create columns where sum other columns based on conditional of other column values?

I need to create columns where sum other columns based on conditional of other column values. The requirement is as below. I have the following table: key code1 code2 code3 code4 value1 value2 v...

Show Detail
PostgreSQL: Autofill columns values by applying math to other values?

On PostgreSQL (using pgAdmin4) I'm trying to make some columns autofill values based on math being applied to other values in the same and/or other table(s). Given the tables below, I want to set :

Show Detail