3

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.

Vikky
  • 752
  • 3
  • 15
  • 35
  • 1
    Almost everything is possible ;) You're talking about Unpivot... – Maciej Los Apr 12 '15 at 19:39
  • http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql – PhillipD Apr 12 '15 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 Apr 12 '15 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 Apr 12 '15 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 Apr 12 '15 at 19:59

1 Answers1

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
void
  • 7,760
  • 3
  • 25
  • 43
  • 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 – Vikky Apr 12 '15 at 21:59
  • 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 – void Apr 12 '15 at 22:04