Sql combining two column into one variable
Use || for concatenating in oracle
SELECT A.*,
K1.AD || K1.SYD as CONTACT,
KR.AD KURUM,
K2.AD SUPERVISORAD,
K2.SYD SUPERVISORSOYAD,
K3.AD REQUIRERAD,
K3.SYD REQUIRERSOYAD
FROM
TD_ACTIVITY A,
KK_KS K1,
KK_KS K2,
KK_KS K3,
KK_KR KR
WHERE A.INS_CONTACT_PERSON = K1.ID
AND A.SUPERVISOR = K2.ID
AND A.REQUIRERID = K3.ID
AND A.INSTITUTIONID = KR.ID
Oracle Combine several columns into one
You can use UNPIVOT:
Oracle Setup:
CREATE TABLE your_table ( spot, id, sunday, monday, tuesday, wednesday, thursday, friday, saturday ) AS
SELECT 'A', 1, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4 FROM DUAL UNION ALL
SELECT 'A', 2, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45 FROM DUAL UNION ALL
SELECT 'A', 3, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5 FROM DUAL;
Query:
SELECT *
FROM your_table
UNPIVOT ( Value FOR Day_of_week IN (
sunday AS 1,
monday AS 2,
tuesday AS 3,
wednesday AS 4,
thursday AS 5,
friday AS 6,
saturday AS 7
) );
Output:
S ID DAY_OF_WEEK VALUE
- -- ----------- -----
A 1 1 .1
A 1 2 .15
A 1 3 .2
A 1 4 .25
A 1 5 .3
A 1 6 .35
A 1 7 .4
A 2 1 .15
A 2 2 .2
A 2 3 .25
A 2 4 .3
A 2 5 .35
A 2 6 .4
A 2 7 .45
A 3 1 .2
A 3 2 .25
A 3 3 .3
A 3 4 .35
A 3 5 .4
A 3 6 .45
A 3 7 .5
Merge multiple columns values in one column in one row Oracle SQL
You don't need a listagg for this, you can just concat all the columns as follows -
select colnum, col1||','||col2||','||col3 as col1234
from columnMerger
COLNUM COL1234
1 a,b,c
Oracle SQL combine two columns into one without UNION
You could try to use UNPIVOT FUNCTION
SELECT
MY_ALIAS
FROM
(
SELECT COL_A, COL_B AS MY_ALIAS
WHERE <lots of conditions here>
) T UNPIVOT (MY_ALIAS FOR COL IN (COL_A, COL_B))
How to merge (combine) 2 columns into 1 in oracle?
concatenate?
select col1 || ' ' || col2 from tablex
Oracle SQL: Merging multiple columns into 1 with conditions
Use NVL2 (or CASE) and concatenate the columns and trim any excess trailing commas:
SELECT Name,
RTRIM(
NVL2(CapacityA,'A,',NULL)
||NVL2(CapacityB,'B,',NULL)
||NVL2(CapacityC,'C',NULL),
','
) AS type,
RTRIM(
NVL2(CapacityA,CapacityA||',',NULL)
||NVL2(CapacityB,CapacityB||',',NULL)
||NVL2(CapacityC,CapacityC,NULL),
','
) AS capacity
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name (name, capacitya, capacityb, capacityc) AS
SELECT 'Plant1', 10, NULL, 20 FROM DUAL UNION ALL
SELECT 'Plant2', NULL, 10, NULL FROM DUAL;
Outputs:
NAME TYPE CAPACITY Plant1 A,C 10,20 Plant2 B 10 Merge Columns into One Column Oracle PL/SQL
If that's the final result you can merge last four columns assuming one column has value and rest are null(sub division columns)
SELECT log_id,
year,
CASE
WHEN sub_division IS NULL AND SUB_DIV3 IS NULL AND SUB_DIV4 IS NULL THEN sub_div
WHEN sub_division IS NULL AND SUB_DIV IS NULL AND SUB_DIV4 IS NULL THEN sub_div3
WHEN sub_division IS NULL AND SUB_DIV IS NULL AND SUB_DIV3 IS NULL THEN sub_div4
ELSE
sub_division
END as sub_division,
display_name
FROM (SELECT DISTINCT L.log_id,
FC.log_yr AS YEAR,
WA.sub_division,
AI.sub_division AS SUB_DIV,
EA.sub_division
AS SUB_DIV3,
FI.sub_division AS SUB_DIV4,
(SELECT display_name
FROM z_sub_division_type a
WHERE a.sub_division = WA.sub_division
OR a.sub_division = AI.sub_division
OR a.sub_division = EA.sub_division
OR a.sub_division = FI.sub_division
) AS DISPLAY_NAME
FROM final_calendar FC
join log L
ON To_date (To_char (L.log_date, 'MM/DD/YYYY'), 'MM/DD/YYYY') =
FC.cal_date
left outer join log_water WA
ON WA.log_id = L.log_id
left outer join log_air AI
ON AI.log_id = L.log_id
left outer join log_earth EA
ON EA.log_id = L.log_id
left outer join log_fire FI
ON FI.log_id = L.log_id)Edit 1:-This sql works assuming atleast one column has value and rest are null
Edit 2:- You can replace case clause with coalesce
coalesce(sub_division,sub_div,sub_div3,sub_div4) as sub_division
Related Topics
Ssis Hidden Sheets as Excel Destination
Row_Number Simulation in SQL Server 2000
Insert Multiple Rows in One Table Based on Number in Another Table
Ef and Transactionscope for Both SQL Server and Oracle Without Escalating/Spanning to Dtc
How to Improve This Mailing Address SQL Server Select Statement
Find Most Common Elements in Array with a Group By
How to Perform the Same Aggregation on Every Column, Without Listing the Columns
Does Ms Access Suppress Primary Key Violations on Inserts
MySQL Duplicates with Load Data Infile
Replace Row Value with Empty String If Duplicate
Grant Access to Just One Schema in Postgresql
Date Calculation with Parameter in Ssis Is Not Giving the Correct Result
SQL Split Column Based on 1 or More Possible Delimiter and Insert in New Table
Quartile/Percentile in Ms Access via SQL with a Group by When Some Values Can Be Null