How to convert multiple row data into column data in sql server -
i have following result sql query:
id atr1 atr2 atr3 atr4 1 bsdf csdfs djk 5 esdds f gds hkkj 8 j ksd lk 9 ads sdf dfse wer now need above result in following format:
s.no 1 2 3 4 id 1 5 8 9 atr1 esdds ads atr2 bsdf f j sdf atr3 csdfs gds ksd dfse atr4 djk hkkj lk wer i unable pivot , unpivot.
in order final result want want unpivot current columns , apply pivot function. before unpivot/pivot data, suggest using row_number() generate unique value each row.
there few different ways can result including using aggregate function case expression, static pivot , dynamic pivot.
aggregate case: can result first using union query convert multiple columns rows, , use aggregate function case expression:
;with cte ( select id, atr1, atr2, atr3, atr4, row_number() over(order id) seq yourtable ) select s_no, max(case when seq = 1 value end) [1], max(case when seq = 2 value end) [2], max(case when seq = 3 value end) [3], max(case when seq = 4 value end) [4] ( select seq, s_no = 'id', value = cast(id varchar(5)), = 1 cte union select seq, s_no = 'atr1', value = atr1, = 2 cte union select seq, s_no = 'atr2', value = atr2, = 3 cte union select seq, s_no = 'atr3', value = atr3, = 4 cte union select seq, s_no = 'atr4', value = atr4, = 5 cte ) d group s_no, order so; see sql fiddle demo
static unpivot/pivot: if have limited number of values want transform can hard-code query. process of unpivot going convert multiple columns id, atr1, atr2, atr3, , atr4 , convert them multiple rows. did not specify version of sql server using can done using unpivot function or using cross apply.
select seq, s_no, value, ( select id, atr1, atr2, atr3, atr4, row_number() over(order id) seq yourtable ) s cross apply ( select 'id', cast(id varchar(5)), 1 union select 'atr1', atr1, 2 union select 'atr2', atr2, 3 union select 'atr3', atr3, 4 union select 'atr4', atr4, 5 ) c (s_no, value, so); see sql fiddle demo. used cross apply union select each of columns , convert them multiple rows. query data following format:
| seq | s_no | value | | | 1 | id | 1 | 1 | | 1 | atr1 | | 2 | | 1 | atr2 | bsdf | 3 | | 1 | atr3 | csdfs | 4 | | 1 | atr4 | djk | 5 | | 2 | id | 5 | 1 | once have data multiple rows, can apply pivot function:
select s_no, [1], [2], [3], [4] ( select seq, s_no, value, ( select id, atr1, atr2, atr3, atr4, row_number() over(order id) seq yourtable ) s cross apply ( select 'id', cast(id varchar(5)), 1 union select 'atr1', atr1, 2 union select 'atr2', atr2, 3 union select 'atr3', atr3, 4 union select 'atr4', atr4, 5 ) c (s_no, value, so) ) d pivot ( max(value) seq in ([1], [2], [3], [4]) ) piv order so; see sql fiddle demo.
dynamic unpivot/pivot: above works great if had known or limited number of new columns want create if have unknown number of values convert columns, want @ using dynamic sql. generate sql string executed final result:
declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select ',' + quotename(seq) ( select row_number() over(order id) seq yourtable )d xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select s_no,' + @cols + ' ( select seq, s_no, value, ( select id, atr1, atr2, atr3, atr4, row_number() over(order id) seq yourtable ) s cross apply ( select ''id'', cast(id varchar(5)), 1 union select ''atr1'', atr1, 2 union select ''atr2'', atr2, 3 union select ''atr3'', atr3, 4 union select ''atr4'', atr4, 5 ) c (s_no, value, so) ) x pivot ( max(value) seq in (' + @cols + ') ) p ' execute sp_executesql @query; see sql fiddle demo. versions give result:
| s_no | 1 | 2 | 3 | 4 | | id | 1 | 5 | 8 | 9 | | atr1 | | esdds | | ads | | atr2 | bsdf | f | j | sdf | | atr3 | csdfs | gds | ksd | dfse | | atr4 | djk | hkkj | lk | wer |
Comments
Post a Comment