MySQL combine two columns into one column
My guess is that you are using MySQL where the +
operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0
.
So try this:
select concat(column1, column2)
Two ways to add a space:
select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)
Combine multiple columns from database into one column?
In older versions of SQL Server, you can use:
SELECT i.IncidentReference as Reference,
s.Name as Site,
STUFF( (SELECT it.Description + ','
FROM IncidentType it
WHERE iit.IncidentTypeId = it.Id
ORDER BY it.Description
FOR XML PATH ('')
), 1, 2, ''
) as IncidentTypes,
i.Description,
FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) as Logged
FROM Incident i JOIN
TEST_USWM_TestAutomation.TEST_USWM.uvw_AvailaibleSites s
ON i.SiteId = s.Id JOIN
IncidentStatus ins
ON i.IncidentStatusId = ins.Id
WHERE s.Id = 1 /*:siteId */ AND
ins.Name = 'Logged' AND
i.CreatedOn BETWEEN DATEADD(DAY, -30, GETUTCDATE()) AND GETUTCDATE();
I don't think the outer GROUP BY
is needed.
Combining Multiple Columns into One based off another field
Use this :
Select MyId,Col1 as CombininedCol from YourTable where Col1 is not null
Union All
Select MyId,Col2 as CombininedCol from YourTable where Col2 is not null
Union All
Select MyId,Col3 as CombininedCol from YourTable where Col3 is not null
Union All
Select MyId,Col4 as CombininedCol from YourTable where Col4 is not null
order by MyId
Tip: The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).
Each select separately get the two columns of MyID and the Cole 1,2, etc., and finally returns them as two columns.
How to combine multiple columns into one column?
SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName
If you don't want duplicate values, use UNION
instead of UNION ALL
.
You can also do this using UNPIVOT
operator
SELECT Column123
FROM
(
SELECT Column1, Column2, Column3
FROM TableName
) AS tmp
UNPIVOT
(
Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;
https://www.w3schools.com/sql/sql_union.asp
https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/
SQL - Combine data from several columns into one column
My favorite way of doing this uses cross apply
:
select v.id
from t cross apply
(values (t.id1), (t.id2), (t.id3)) v(id);
Like the version using unpivot
this only reads the table once. A version using union all
would scan the table three times. However, cross apply
is much more powerful than unpivot
and requires less typing.
How to combine multiple columns into one column and its data into another column?
I think you want unpivot
(see also this question):
create table phones_wide (customerid int, "1" varchar(20), "2" varchar(20), "3" varchar(20), "4" varchar(20), "5" varchar(20), "6" varchar(20), "7" varchar(20));
insert into phones_wide values (1234, '123-4342', '223-4342', '323-4342', '423-4342', '523-4342', '623-4342', '723-4342')
select PhoneType, PhoneNumber
from phones_wide
unpivot
(
PhoneNumber
for PhoneType in ("1","2","3","4","5","6","7")
) u;
drop table phones_wide
Output:
PhoneType PhoneNumber
1 123-4342
2 223-4342
3 323-4342
4 423-4342
5 523-4342
6 623-4342
7 723-4342
How to combine multiple columns into one column in kdb?
One way would be to use #
(take) which on tables will return a subset of columns. As a table in kdb is simply a list of dicts, can then use value each
on this table to get the values for each row:
q)table:([]time:9 11;Bid1px:4 5;Bid2px:7 3;Bid3px:6 8)
q)Bidcols:`Bid1px`Bid2px`Bid3px;
q)// using just #, all_bid column is a table so each row is a dict
q)update all_bid:Bidcols#table from table
time Bid1px Bid2px Bid3px all_bid
-----------------------------------------------------
9 4 7 6 `Bid1px`Bid2px`Bid3px!4 7 6
11 5 3 8 `Bid1px`Bid2px`Bid3px!5 3 8
q)// adding value each gives us the final desired result
q)update all_bid:value each Bidcols#table from table
time Bid1px Bid2px Bid3px all_bid
---------------------------------
9 4 7 6 4 7 6
11 5 3 8 5 3 8
Combine multiple columns into a single column in Postgres
In Postgres, you can unpivot with a lateral join:
select t.id, m.month, m.qty
from mytable t
cross join lateral (values (t.Month1, 'Month1'), (t.Month2, 'Month2')) as m(qty, month)
order by t.id, m.month
Demo on DB Fiddle:
id | month | qty
:- | :----- | --:
A | Month1 | 2
A | Month2 | 3
B | Month1 | 4
B | Month2 | 5
Merge multiple columns into one column with multiple rows
I think you need something like this:
SELECT ID, 'A' as Letter FROM table WHERE A=1
UNION ALL
SELECT ID, 'B' as Letter FROM table WHERE B=1
UNION ALL
SELECT ID, 'C'as Letter FROM table WHERE C=1
ORDER BY ID, Letter
Merge multiple columns in one single column SQL
select * from
(
select 'Pencil Sales' as Name, sum(pencilsales) as sales from table1
union all
select 'Notebook Sales', sum(notebooksales) from table1
union all
select 'Pen Sales', sum(pensales) from table1
) t order by sales desc
Related Topics
How to Make SQL Query Result Show With 2 Decimals
Sql Join: Selecting the Last Records in a One-To-Many Relationship
Sql Query to Return Only First Occurance of One Column Value
Retrieve String Between HTML Tags Using Regex
Sql Get Parent Where Children Have Specific Values
Insert Value into Table If Conidition Is Met At Least One Time
Row Numbers in Query Result Using Microsoft Access
Pg Copy Error: Invalid Input Syntax for Integer
How Select Max(Salary) of Employee Each Department With Employee_Id and Emp_Name
How to Retrieve Records for Last 30 Minutes in Ms SQL
How to Select All the Columns of a Table Except One Column
Create a Query That Will Display Employee Name and Department Number
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
How to Select the Last Record of a Table in SQL
How to Modify Query to Remove Double-Quotes from All Columns
H2 Database. How to Convert Date to Seconds in SQL
How to Use Return Value of Insert...Returning in Another Insert