[Solved] Dynamic convert Row to Column using group by

by


try this,

    create table  #tmp (MR_ID varchar(50),DR_ID int)
insert into  #tmp VALUES
('MR_123', 1),('MR_123', 3),('MR_124', 4),('MR_124', 5)
,('MR_124', 6),('MR_125', 0)

 declare @DRCol varchar(50)
 declare @Prefix varchar(20)='DR_'
 ;With CTE as
 (
select *
,ROW_NUMBER()over(partition by MR_ID order by DR_ID)rn
 from #tmp
 )

 select top 1 
@DRCol=stuff((select ','+'['+@Prefix+cast(rn as varchar)+']' 
 from cte c1 where c.mr_id=c1.mr_id for xml path('')),1,1,'')
 from cte c
 where c.mr_id=(select top 1 mr_id from cte c1 order by rn desc)


 declare @Sql varchar(2000)=''

 set @Sql="   SELECT p.*
FROM
(
  SELECT *
          ,"''+@Prefix+'''+ cast(ROW_NUMBER() OVER(PARTITION BY MR_ID ORDER BY DR_ID) as varchar(max))  columnName
    FROM #tmp AS m
    ) AS t
PIVOT(MAX(DR_ID) FOR columnName IN('+@DRCol+')) AS p'
print @sql
exec(@sql)
drop table #tmp

8

solved Dynamic convert Row to Column using group by