Dynamic convert Row to Column using group by

Question

I am looking for a code which can transpose row to column using the group by, below is my table and desire output.

Table:

MR_ID  |  DR_ID
--------|--------
MR_123  |   1
MR_123  |   3
MR_124  |   4
MR_124  |   5
MR_124  |   6
MR_125  |   0

Desire Output:

MR_ID  |  DR_ID_1 |  DR_ID_2 | DR_ID_3 
--------|----------|----------|---------
MR_123  |     1    |    2     |   NULL
MR_124  |     4    |    5     |    6
MR_125  |     0    |   NULL   |   NULL

Here I want each separate column for each DR_ID with increment of the number, this should be dynamic.

score 0 · Answer 1 · answered Apr 27 '17 at 08:21

Try it like this:

DECLARE @mockup TABLE(MR_ID VARCHAR(10),DR_ID INT);
INSERT INTO @mockup VALUES
 ('MR_123',1)
,('MR_123',3)
,('MR_124',4)
,('MR_124',5)
,('MR_124',6)
,('MR_125',0);

SELECT p.*
FROM
(
    SELECT *
          ,'DR_ID_' + CAST(ROW_NUMBER() OVER(PARTITION BY MR_ID ORDER BY DR_ID) AS VARCHAR(MAX)) AS columnName
    FROM @mockup AS m
) AS t
PIVOT(MAX(DR_ID) FOR columnName IN(DR_ID_1,DR_ID_2,DR_ID_3,DR_ID_4)) AS p;

The "dynamic column name" is created with ROW_NUMBER()

score 0 · Answer 2 · answered Apr 27 '17 at 08:24

0

You can also use dynamic sql query.

Query

declare @sql as varchar(max);
select @sql = 'select t.MR_ID, ' + stuff((
    select distinct ', max(case t.[rn] when ' 
    + cast(t.[rn] as varchar(10)) + ' then t.[DR_ID] end) 
    [DR_ID' + cast(t.[rn] as varchar(10)) + ']' 
    from (
        select [rn] = row_number() 
        over(partition by MR_ID order by DR_ID), *
        from your_table_name
    )t
    for xml path('')
), 1, 2, '')
+ ' from (select [rn] = row_number() over(partition by MR_ID order by DR_ID), * 
  from your_table_name)t
  group by t.[MR_ID];';

exec(@sql);

Find a demo here

answered Apr 27 '17 at 08:24

Ullas

11,450
4
33
50

Hi Ullas thanks for the response, could you do some more help to understand it. – Jayank Apr 27 '17 at 09:37
this code is not working on more than 38 DR_ID, as it cross the variable store limit. – Jayank May 01 '17 at 07:14
I am getting these three error. 1) "Cannot assign a default value to a local variable." 2) "Must declare the scalar variable "@Prefix"" , and 3) "Incorrect syntax near the keyword 'order'." – Jayank May 01 '17 at 09:51
@Jayank : I haven't used a variable called `@Prefix` in my query. – Ullas May 01 '17 at 09:55

KumarHarsh · Accepted Answer · 2017-04-27T10:17:49.430

0

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

edited Apr 27 '17 at 10:17

answered Apr 27 '17 at 08:59

KumarHarsh

5,046
1
18
22

1) You are missing a `+` before the `(CAST(ROW_NUMBER`. 2) You are missing the `AS columnName` after `MAX)))` and 3) The dynamic approach cannot know a declared table variable as it is out of scope... – Shnugo Apr 27 '17 at 10:13
@Shnugo,Thank.i forgot that in dynamic sql you don't use table variable.that was the only mistake.secondly no doubt i copied part of your sql . – KumarHarsh Apr 27 '17 at 10:19
@KumarHarsh, I faced error for Order by (order by rn) and scalar variable error for '@Perfix' – Jayank May 01 '17 at 07:11
i just copied my script and ran without any error.throw more detail. – KumarHarsh May 01 '17 at 08:12
I am getting these three error. 1) "Cannot assign a default value to a local variable." 2) "Must declare the scalar variable "@Prefix"" , and 3) "Incorrect syntax near the keyword 'order'.", in the subquery-- while setting value for @DRCol – Jayank May 01 '17 at 09:54
Hye @KumarHarsh, its done, actually it was version issue. thanks – Jayank May 01 '17 at 10:04
oh what version are you using ? – KumarHarsh May 01 '17 at 10:04
Hi Harsh could you help me on one more question https://stackoverflow.com/questions/44188006/manupilating-previous-month-data-according-to-current-month – Jayank May 26 '17 at 14:25

Dynamic convert Row to Column using group by

3 Answers3

Linked