CODEWITHSUNDEEP
sqlsql-server
a.ali
a.ali

Reputation: 33

How can I combine different values from same column in one column?

I want to create this table and use it in my report.

I have a table like this:

sum commodity unit
100 a4 paper x
200 a5 paper y
7 a4 paper z
410 pencil e
300 a5 paper w

How can I create a table like this from the above table?

sum commodity unit
107 a4 paper x-z
500 a5 paper y-w
410 pencil e

Upvotes: 1

Views: 1024

Answers (2)

Utsav
Utsav

Reputation: 8143

As you havn't mentioned your RDBMS, you can use something like this 

select sum(sum),
commodity,
group_concat(unit SEPARATOR '-') from commo2 t
group by commodity;

GROUP_CONCAT works on MYSQL. You can search of alternative of group_concat in your RDBMS, like in Oracle you can use LISTAGG instead. But the logic is same

Demo in MYSQL

http://rextester.com/ZXZA16316

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

If use SQL server means,use below query 

CREATE TABLE table2(sums INT, commodity VARCHAR(100),unit VARCHAR(2))

INSERT INTO table2(sums , commodity ,unit)
SELECT 100,'a4 paper','x' UNION ALL
SELECT 200,'a5 paper','y' UNION ALL
SELECT 7,'a4 paper','z' UNION ALL
SELECT 410,'pencil','e' UNION ALL
SELECT 300,'a5 paper','w' 

 SELECT SUM(sums) , commodity , STUFF( (SELECT '-' + unit FROM table2 I 
 WHERE I.commodity = O.commodity FOR XML PATH('')),1,1,'') unit
 FROM table2 O
 GROUP BY commodity

Upvotes: 2

Related Questions