SQL Server combine multiple rows into one

Question

I want multiple rows to be merged into single row

The client with Inv_ID = 188, 198 and 82 should be merged into one row

Result should have two rows..

ClientID | Input(188) | Input(198 | Input(82)             |Date(188)         |Date (198)
--------------------------------------------------------------------
133      | Yes        | yes       | Referred to comm Pres | 2016-08-16 01:00 | 2016-8-01
133      | yes        | yes       | Referred to comm Pres | 2016-08-17 00:00 | 2016-08-17

Can anyone please help me ?

Thank you.

What parts of those rows do you want to show? E.g. which value of `M_ID`, what value of `Input` ?? — marc_s, Aug 23 '16 at 04:52
I want result to be as follows ClientID | Input(188) | Input(198 | Input(82) |Date(188) |Date (198) 133 |Yes |yes |Referred to comm Pres | 2016-08-16 01:00| 2016-8-01: 133| yes | yes| Referred to comm Pres |2016-08-17 00:00 |2016-08-17 — adi_2010, Aug 23 '16 at 04:58
@NEER I want to combine client who has Inv_id = 188,198,82 together , the result of the query should give me client id, input of 188,198 and 82 respectively , date of 188,198 respectively — adi_2010, Aug 23 '16 at 06:48
What in the data differentiates the blue highlighted block of 188,198,88s from the second block of 188,198,88s? — P.Salmon, Aug 23 '16 at 12:07
@P.Salmon The date field can differ, there can be different date value for each row — adi_2010, Aug 23 '16 at 12:26

score 0 · Accepted Answer · answered Aug 23 '16 at 14:35

I think the key to this is understanding which blocks the 188,198 and 88s fall into. In the cte below i am working on the basis that an 88 always terminates a block and am assigning the rowid of the 88 to all rowids less than it - thus enabling the group by.

declare @t table(client_id int, m_id int,chid int,inv_id int,input varchar(20),dt datetime,rowid int)
insert into @t values
(133,928,9581,188,'yes_b1','2016-08-16 01:00:00:000',1),
(133,929,9581,198,'yes_b1','2016-08-16 01:10:00:000',2),
(133,930,9581,82,'referred_b1','2016-08-16 01:30:00:000',3),
(133,935,9584,188,'yes_b2','2016-08-16 01:00:00:000',5),
(133,936,9584,198,'yes_b2','2016-08-16 01:00:00:000',6),
(133,937,9584,82,'referred_b2','2016-08-16 01:00:00:000',7)

;with cte as
(
select s.*,
        lag(s.hi,1,0) over (order by s.inv_id) as lo 
from
(
select  inv_id,rowid as hi
from @t
where inv_id = 82
)s
)
select t.client_id,
        max(case when t.inv_id = 188 then input end) 'input(188)',
        max(case when t.inv_id = 198 then input end) 'input(198)',
        max(case when t.inv_id = 82  then input end) 'input(82)',
        max(case when t.inv_id = 188 then dt end) 'date(188)',
        max(case when t.inv_id = 198 then dt end) 'date(198)',
        max(case when t.inv_id = 82  then dt end) 'date(82)' 
from @t t
join cte on rowid <= cte.hi and rowid > cte.lo
group by client_id,cte.hi

Result

client_id   input(188)           input(198)           input(82)            date(188)               date(198)               date(82)
----------- -------------------- -------------------- -------------------- ----------------------- ----------------------- -----------------------
        133 yes_b1               yes_b1               referred_b1          2016-08-16 01:00:00.000 2016-08-16 01:10:00.000 2016-08-16 01:30:00.000
        133 yes_b2               yes_b2               referred_b2          2016-08-16 01:00:00.000 2016-08-16 01:00:00.000 2016-08-16 01:00:00.000

SQL Server combine multiple rows into one

1 Answers1

Linked