get result of 2 rows value in one row

Question

result of a join query-

SELECT st_stock.name, st_stock.price, 
       FORMAT(st_stock.quantity,0), 
       st_stock.exp_date, 
       admin.username 
FROM `st_stock` 
  INNER JOIN `admin` ON FIND_IN_SET(admin.ID,st_stock.broker_id) AND st_stock.id='2'

Sample Data

name        price     asjdb       exp_date            username
5.HK (HSBC) 74.40 HKD 100,000,000 2018-07-27 17:00:00 broker2
5.HK (HSBC) 74.40 HKD 100,000,000 2018-07-27 17:00:00 broker3

Expected Output

 5.HK (HSBC) 74.40 HKD 100,000,000 2018-07-27 17:00:00 broker2,broker3

my result should be--- 5.HK (HSBC) 74.40 HKD 100,000,000 2018-07-27 17:00:00 broker2,broker3 — Smritimay Debnath, Jul 11 '18 at 13:17
[edit] your question. Do not add additional information as (unformatted) comments — , Jul 11 '18 at 13:19
Possible duplicate of [Concatenate row values T-SQL](https://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql) — Sharad Venkataraman, Jul 11 '18 at 13:21
https://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row — Sangsom, Jul 11 '18 at 13:21
Please add your comment with an expectation into a question and format it properly. — Yuriy Tsarkov, Jul 11 '18 at 13:24
actually that result which i showed you, that get from a join query- "SELECT st_stock.name, st_stock.price, FORMAT(st_stock.quantity,0), st_stock.exp_date,admin.username FROM `st_stock` INNER JOIN `admin` ON FIND_IN_SET(admin.ID,st_stock.broker_id) AND st_stock.id='2'" — Smritimay Debnath, Jul 11 '18 at 13:26
Despite the unclearence I believe that the best way is to look at the `PIVOT` function — Yuriy Tsarkov, Jul 11 '18 at 13:26

Zerodf · Answer 1 · 2018-07-11T13:32:29.500

0

You did not specify the SQL engine you are using. When it comes to more advanced aggregate and windowing functions, implementations vary across database engines.

In PostgreSQL for example, you would use the string_agg aggregate function:

SELECT name, price, asjdb, exp_date, string_agg(username,',') AS username
FROM d
GROUP BY name, price, asjdb, exp_date;

For other database engines you would use:

Oracle: LISTAGG
SQL Server: STRINGAGG
MySQL: GROUP_CONCAT

Further Reading: ListAGG in SQLSERVER

edited Jul 11 '18 at 13:32

answered Jul 11 '18 at 13:26

Zerodf

2,208
18
26

Thanks for the engine tag! – Zerodf Jul 11 '18 at 13:33

score 0 · Accepted Answer · answered Jul 11 '18 at 13:53

0

If this is sql,

 SELECT st_stock.name, st_stock.price, 
           FORMAT(st_stock.quantity,0), 
           st_stock.exp_date, 
           GROUP_CONCAT(admin.username)
    FROM `st_stock` 
      INNER JOIN `admin` ON FIND_IN_SET(admin.ID,st_stock.broker_id) AND st_stock.id='2' GROUP BY st_stock.name

answered Jul 11 '18 at 13:53

varman

8,704
5
19
53

@SmritimayDebnath its workign fine,right? Make sure you to click up arrow to vote good ans :D – varman Jul 11 '18 at 14:05

get result of 2 rows value in one row

2 Answers2