Combine Multiple Columns from Database into One Column

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



Leave a reply



Submit