SQL select multiple rows as one row

Question

Table

ID	Date	Model	Price
6255	2018-01-31	HZH98CC	435.34
6256	2018-01-31	CVVCDE7	23.24
6257	2018-01-31	WWRT423	24.24
6258	2018-02-14	DT4	43.23
6259	2018-02-14	D42C	243.2

How can I use query/view to get All the records from the DB table and combine rows with same date into one row? I am not sure how to start on it. I've tried FOR XML but I am not getting the result I want

SELECT DISTINCT transactions.[id], 
    SUBSTRING(
        (
            SELECT ','+ transactions.model  AS [text()]
            FROM transactions
            FOR XML PATH ('')
        ), 2, 1000) transactions
FROM transactions

Which returns

ID	Model
6255	HZH98CC,CVVCDE7,WWRT423,DT4,D42C

Expected Outcome

ID	Date	Model	Price
6255,6256,6257	2018-01-31	HZH98CC,CVVCDE7,WWRT423	435.34,23.24,24.24
6258,6259	2018-02-14	DT4,D42C	43.23,243.2

Is this in anyway possible or do I have to do

Select distinct [date]
From transactions Order by [date]

and the use the resulting dates to SELECT the matching rows and combine them in code later?

I need all cells from the matching rows (i.e. rows with the same date) to be concatenated into the cell of that column.

Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Yong Shun, Mar 27 '21 at 04:05
You can use this. [https://stackoverflow.com/questions/27712374/how-to-convert-multiple-rows-to-one-row-in-sql-server](https://stackoverflow.com/questions/27712374/how-to-convert-multiple-rows-to-one-row-in-sql-server) — Gowthaman, Mar 27 '21 at 04:06
Please refine (or simplify) your expected outcome example. As it stands it makes no sense to aggregate the model strings and randomly include only one of their prices. — AlanK, Mar 27 '21 at 04:28

score 4 · Accepted Answer · answered Mar 27 '21 at 13:02

Since SQL Server 2017, SQL Server has supported string_agg():

select date,
       string_agg(id, ',') within group (order by id) as ids,
       string_agg(model, ',') within group (order by id) as model,
       string_agg(price, ',') within group (order by id) as prices
from transactions
group by date;

In older versions of SQL Server, you have to use the XML work-around.

score 0 · Answer 2 · answered Mar 28 '21 at 18:00

Example using XML for older versions of SQL Server that do not have string_agg function.

DECLARE @t TABLE
    (
    id varchar(10),
    date date, 
    model varchar(10),
    price varchar(10)
    )

INSERT INTO @t
select 6255 ,'2018-01-31',  'HZH98CC',  435.34 union all
select 6256 ,'2018-01-31',  'CVVCDE7',  23.24 union all
select 6257 ,'2018-01-31',  'WWRT423',  24.24 union all
select 6258 ,'2018-02-14',  'DT4',  43.23 union all
select 6259 ,'2018-02-14',  'D42C', 243.2;

 SELECT 
  stuff( ( SELECT ','+ [id] FROM @t WHERE date = t1.date FOR XML PATH('') 
                     ),1,1,'') id,
  date,  
  stuff( ( SELECT ','+ [model] FROM @t WHERE date = t1.date FOR XML PATH('') 
                     ),1,1,'') model,
  stuff( ( SELECT ','+ [price] FROM @t WHERE date = t1.date FOR XML PATH('') 
                     ),1,1,'')  price
FROM (SELECT distinct date FROM @t ) t1

SQL select multiple rows as one row

2 Answers2