CODEWITHSUNDEEP
sqlsql-servert-sql
XcOder
XcOder

Reputation: 157

SQL select multiple rows as one row

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.

Upvotes: 1

Views: 8895

Answers (2)

mountainX
mountainX

Reputation: 1

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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.

Upvotes: 5

Related Questions