Combine multiple rows into one row in sql

Transact-SQL
1

Need help to merge all the rows into a single row, can anybody help me please!!
Here's my query and OUTPUT
SELECT BNBEN.EMPLOYEE AS SUB_EMPLOYEE,
BNBEN.PLAN_CODE AS SUB_PLAN_CODE,
BNBEN.EMP_COST AS SUB_EMP_COST
FROM BNBEN
WHERE ((BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_TYPE='RS')
OR (BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_CODE IN ('MEDW','MED1','MED4')))

Sub_Employee Sub_Plan_Code Sub_Emp_Cost
166 MED4 57.87
166 FSAM 100.00
166 FSDW 0.00
166 HSAV 230.77
166 NLSF 0.00
172 MED4 22.80
172 FSAD 192.31
172 FSMW 0.00
172 HSAV 269.23
172 LSFS 101.92

OUTPUT SHOULD BE LIKE BELOW
Sub_Emp MED4 FSAM FSDW HSAV NLSF FSAD FSMW LSFS
166 57.87 100.00 0.00 230.77 0.00 null null null
172 22.80 null null 269.23 null 192.31 0.00 101.92

2

Use same princip as this

3

hi

i know this post was created 27 days ago

I tried to do it
using
recursive cte

I think its also possible
with PIVOT ...
could be wrong

Please let me know if i am missing anything
I notice nulls in expected output but did not think about it

drop create data 
use tempdb
go 


drop table data
go 

create table data
(
Sub_Employee int , 
Sub_Plan_Code varchar(100) , 
Sub_Emp_Cost decimal (10,2) 
)
go 

insert into data select  166,'MED4',57.87
insert into data select  166,'FSAM',100.00
insert into data select  166,'FSDW',0.00
insert into data select  166,'HSAV',230.77
insert into data select  166,'NLSF',0.00
insert into data select  172,'MED4',22.80
insert into data select  172,'FSAD',192.31
insert into data select  172,'FSMW',0.00
insert into data select  172,'HSAV',269.23
insert into data select  172,'LSFS',101.92
go
SQL 
; WITH rownumber_adding_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) rn, 
                sub_plan_code 
         FROM   data), 
     recursive_cte 
     AS (SELECT rn, 
                Cast('sub_emp' + ' ' + sub_plan_code AS VARCHAR(100)) ok_code 
         FROM   rownumber_adding_cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.rn, 
                Cast(b.ok_code + ' ' + a.sub_plan_code AS VARCHAR(100)) 
         FROM   rownumber_adding_cte a 
                JOIN recursive_cte b 
                  ON a.rn = b.rn + 1), 
     rownumber_adding_cte_1 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) rn, 
                sub_employee, 
                sub_emp_cost 
         FROM   data), 
     recursive_cte_1 
     AS (SELECT rn, 
                Cast(( Cast( sub_employee AS VARCHAR(100)) + ' ' 
                       + Cast( sub_emp_cost AS VARCHAR(100)) + ' ' ) AS VARCHAR( 
                     100)) 
                AS 
                    ok_code 
         FROM   rownumber_adding_cte_1 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.rn, 
                Cast (( Cast(b.ok_code AS VARCHAR(100)) + ' ' 
                        + Cast( a.sub_employee AS VARCHAR(100)) + ' ' 
                        + Cast( a.sub_emp_cost AS VARCHAR(100)) + ' ' ) AS 
                      VARCHAR( 
                      100)) 
         FROM   rownumber_adding_cte_1 a 
                JOIN recursive_cte_1 b 
                  ON a.rn = b.rn + 1) 
SELECT a.ok_code + b.ok_code 
FROM   (SELECT ok_code 
        FROM   recursive_cte 
        WHERE  rn = (SELECT Max(rn) 
                     FROM   recursive_cte))a, 
       (SELECT ok_code 
        FROM   recursive_cte_1 
        WHERE  rn = (SELECT Max(rn) 
                     FROM   recursive_cte_1))b 

go
Result

image
image.png1133×118 21.3 KB

4

Very easy with dynamic pivot

Drop table if exists #t

create table #t
(
Sub_Employee int, 
Sub_Plan_Code varchar(10),
Sub_Emp_Cost decimal (10,2)
)

insert into  #t values
(166,'MED4',57.87),
(166,'FSAM',100.00),
(166,'FSDW',0.00),
(166,'HSAV',230.77),
(166,'NLSF',0.00),
(172,'MED4',22.80),
(172,'FSAD',192.31),
(172,'FSMW',0.00),
(172,'HSAV',269.23),
(172,'LSFS',101.92)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Sub_Plan_Code) 
            FROM #t c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Sub_Employee, ' + @cols + ' from 
            (
                select Sub_Employee
                    , Sub_Emp_Cost
                    , Sub_Plan_Code
                from #t
           ) x
            pivot 
            (
                 max(Sub_Emp_Cost)
                for Sub_Plan_Code in (' + @cols + ')
            ) p '


execute(@query)
5

No, this case is different from other case. I just post this morning. Can you please read it again. Thank you very much for your help.