I have following result from my sql query:
Id atr1 atr2 atr3 atr4
1 a bsdf csdfs djk
5 esdds f gds hkkj
8 i j ksd lk
9 ads sdf dfse wer
Now I need this above result in following format:
S.no 1 2 3 4
Id 1 5 8 9
atr1 a esdds i ads
atr2 bsdf f j sdf
atr3 csdfs gds ksd dfse
atr4 djk hkkj lk wer
I am unable to do it with Pivot and Unpivot.
In order to get the final result that you want you will want to unpivot your current columns and then apply the pivot function. But before you unpivot/pivot your data, I would suggest using row_number() to generate a unique value for each row.
There are a few different ways that you can get the result including using an aggregate function with CASE expression, static PIVOT and dynamic PIVOT.
Aggregate with CASE: You can get the result by first using a UNION ALL query to convert the multiple columns into rows, and then use an aggregate function with a CASE expression:
;with cte as
(
select id, atr1, atr2, atr3, atr4,
row_number() over(order by id) seq
from yourtable
)
select s_no,
max(case when seq = 1 then value end) [1],
max(case when seq = 2 then value end) [2],
max(case when seq = 3 then value end) [3],
max(case when seq = 4 then value end) [4]
from
(
select seq, s_no = 'id', value = cast(id as varchar(5)), so = 1
from cte
union all
select seq, s_no = 'atr1', value = atr1, so = 2
from cte
union all
select seq, s_no = 'atr2', value = atr2, so = 3
from cte
union all
select seq, s_no = 'atr3', value = atr3, so = 4
from cte
union all
select seq, s_no = 'atr4', value = atr4, so = 5
from cte
) d
group by s_no, so
order by so;
Static UNPIVOT/PIVOT: If you have a limited number of values that you want to transform then you can hard-code the query. The process of unpivot is going to convert your multiple columns id, atr1, atr2, atr3, and atr4 and convert them into multiple rows. You did not specify what version of SQL Server you are using but this can be done using the UNPIVOT function or using CROSS APPLY.
select seq, s_no, value, so
from
(
select id, atr1, atr2, atr3, atr4,
row_number() over(order by id) seq
from yourtable
) s
cross apply
(
select 'id', cast(id as varchar(5)), 1 union all
select 'atr1', atr1, 2 union all
select 'atr2', atr2, 3 union all
select 'atr3', atr3, 4 union all
select 'atr4', atr4, 5
) c (s_no, value, so);
See SQL Fiddle with Demo. I used CROSS APPLY with a UNION ALL to select each of your columns and convert them into multiple rows. This query will get your data into the following format:
| SEQ | S_NO | VALUE | SO |
| 1 | id | 1 | 1 |
| 1 | atr1 | a | 2 |
| 1 | atr2 | bsdf | 3 |
| 1 | atr3 | csdfs | 4 |
| 1 | atr4 | djk | 5 |
| 2 | id | 5 | 1 |
Once you have the data into multiple rows, then you can apply the PIVOT function:
select s_no, [1], [2], [3], [4]
from
(
select seq, s_no, value, so
from
(
select id, atr1, atr2, atr3, atr4,
row_number() over(order by id) seq
from yourtable
) s
cross apply
(
select 'id', cast(id as varchar(5)), 1 union all
select 'atr1', atr1, 2 union all
select 'atr2', atr2, 3 union all
select 'atr3', atr3, 4 union all
select 'atr4', atr4, 5
) c (s_no, value, so)
) d
pivot
(
max(value)
for seq in ([1], [2], [3], [4])
) piv
order by so;
See SQL Fiddle with Demo.
Dynamic UNPIVOT/PIVOT: The above works great if you had an known or limited number of new columns that you want to create but if you have an unknown number of values to convert into columns, then you will want to look at using dynamic SQL. This will generate a sql string that will then be executed to get you the final result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(seq)
from
(
select row_number() over(order by id) seq
from yourtable
)d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT s_no,' + @cols + '
from
(
select seq, s_no, value, so
from
(
select id, atr1, atr2, atr3, atr4,
row_number() over(order by id) seq
from yourtable
) s
cross apply
(
select ''id'', cast(id as varchar(5)), 1 union all
select ''atr1'', atr1, 2 union all
select ''atr2'', atr2, 3 union all
select ''atr3'', atr3, 4 union all
select ''atr4'', atr4, 5
) c (s_no, value, so)
) x
pivot
(
max(value)
for seq in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. All versions will give a result:
| S_NO | 1 | 2 | 3 | 4 |
| id | 1 | 5 | 8 | 9 |
| atr1 | a | esdds | i | ads |
| atr2 | bsdf | f | j | sdf |
| atr3 | csdfs | gds | ksd | dfse |
| atr4 | djk | hkkj | lk | wer |