How to output columns as rows in a select?

Question

I have a table like this

Col1     Col2     Col3   Col4
1        2        3      3
2        23       4      7
3        3        12     4

And i want output like this

Col1    1   2   3
Col2    2   23  3
Col3    3   4   12
Col4    3   7   4

I have googled and found some example over pivot in sql to transform rows into columns, but they are based on aggregation of some values in table. I dont have any such values on which i can put aggregation.The actual no of columns are around 30 and are of different datatype Any ideas will be a great help.

Almost everything is possible ;) You're talking about Unpivot... — Maciej Los
– Maciej Los, Commented Apr 12, 2015 at 19:39
This is a de-normalization operation and can only be done on a non-dynamic query for a fixed number of rows - is it correct to assume there is only three rows? Aggregation is used because normally the rows in such queries are 0..n, for any n, but it has to be mapped 1..z, for some query-fixed value of z. You can still use PIVOT and just aggregate over each row individually (making x = AVG(x)) - but the number of rows must be fixed. — user2864740
– user2864740, Commented Apr 12, 2015 at 19:53
there are many rows in actual scenario but not much when use with some foreign key, but it has many columns with different type of data. i am still trying to sort this using dynamic query operation — Vikky
– Vikky, Commented Apr 12, 2015 at 19:56
My recommendation would be to start with a normalized source (the 2nd form shown) and then convert to the first form when needed. This makes the transformation easier as the column names are trivially identified - this may even eliminate the need for such a transformation. (My first comment should have read 'de-de-normalization', if the columns represent multiplicity.) — user2864740
– user2864740, Commented Apr 12, 2015 at 19:59

void · Accepted Answer · 2015-04-12 20:58:55Z

1

I want to say another way rather than using pivot or unpivot, you can do it with dynamic sql also, it does not matter how many rows or columns the table has, lets check together:

Schema:

create table tblSO(Col1  int,   Col2  int,   Col3 int,   Col4 int)
insert into tblSO values
(1,        2,        3,      3),
(2,        23,       4,      7),
(3,        3 ,       12,     4);

now with using row_number and a temptable (or table variable) within dynamic sql you can achieve the result you want:

declare @counter int = 1
declare @counter2 int
set @counter2=(select count(*) from tblSO)

declare @Sql varchar(max)='create table #rotate (colname varchar(20) '
while @counter<=@counter2
begin
    set @Sql=@Sql+', val_'+cast(@counter as varchar)+' int'
    set @counter=@counter+1
end 
set @counter =1
set @Sql=@Sql+');
insert into #rotate(colname) 
values (''Col1''),(''Col2''),(''Col3''),(''Col4'');
'

set @Sql = @Sql + '
create table #cte(col1  int,col2  int,col3 int,col4 int,rn int)
insert into #cte
select col1,col2,col3,col4,
       row_number() over(order by Col1) rn
from tblSO;
'

while @counter<=@counter2
begin
declare @cl varchar(50)=cast(@counter as varchar)
set @Sql=@Sql + '
 update #rotate set val_'+@cl+'= 
    (select col1 from #cte where rn='+@cl+') where colname=''Col1''
 update #rotate set val_'+@cl+'= 
    (select col2 from #cte where rn='+@cl+') where colname=''Col2''
 update #rotate set val_'+@cl+'= 
    (select col3 from #cte where rn='+@cl+') where colname=''Col3''
 update #rotate set val_'+@cl+'= 
    (select col4 from #cte where rn='+@cl+') where colname=''Col4'' '
set @counter=@counter+1
end
set @Sql=@Sql + ' select * from #rotate
                  drop table #rotate
                  drop table #cte'
exec(@Sql)

Output:

Col1    1   2   3
Col2    2   23  3
Col3    3   4   12
Col4    3   7   4

answered Apr 12, 2015 at 20:58

void

7,9503 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikky Over a year ago

problem is that in actual scenario, the datatype of columns are diffrent i.e. some are varchar, some are bigint and some are decimal(18,2) and bit and date as well

void Over a year ago

this method works for them also, just put the varchar values inside two 's ''varchar column '' in the dynamic section, you can give a sample data by sqlfidde

Collectives™ on Stack Overflow

How to output columns as rows in a select?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related