Swaping Row to column
-
CREATE table Table1
(
Col1 varchar(100),
Col2 varchar(100),
Col3 varchar(100)
)
INSERT INTO Table1
SELECT 'Data1','Data2','Data3'
UNION ALL
SELECT 'Data4','Data5','Data6'
UNION ALL
SELECT 'Data7','Data8','Data9'
SELECT * FROM Table1
Thanks,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
ACTUAL Col1 Col2 Col3 1 Data1 Data2 Data3 2 Data4 Data5 Data6 3 Data7 Data8 Data9 DESIRED Col1 Col2 Col3 1 Data1 Data4 Data7 2 Data2 Data5 Data8 3 Data3 Data6 Data9 Regards,
vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
Have you searched this site for information? This is a frequently asked question and there are several EXCELLENT ways of doing this already posted....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hunting has been done long back - 2 years before. If you don't mind, kindly provide the link if already posted.
Well, hunt me if you can..?
Vivek - HUB of Hunting Information.
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
Go to the search page and type in "Cross Tab". You will get more hits than I could post...
I wasn't born stupid - I had to study.
-
You can test the following script
drop table table1
drop table tableTmp
drop table table2
CREATE table Table1
(
Col1 varchar(100),
Col2 varchar(100),
Col3 varchar(100)
)
insert table1 values ('Data1','Data2','Data3')
insert table1 values ('Data4','Data5','Data6')
insert table1 values ('Data7','Data8','Data9')
declare @nRow int
select @nRow = count(*) from Table1
declare @nCol int
select @nCol = max(colid) from syscolumns where id = object_id('table1')
print @nRow
declare @ddl varchar(8000)
set @ddl = 'CREATE table TableTmp(Col varchar(100), RowNum int identity(1,1))
CREATE table table2(RowNum int identity(1,1),'
declare @loop int
set @loop = 1
while @loop <= @nRow
begin
set @ddl = @ddl + 'col' + convert(varchar(3), @loop) + ' varchar(100),'
set @loop = @loop + 1
end
set @ddl = @ddl + ')'
set @ddl = replace(@ddl,',)',')')
exec(@ddl)
Declare @loopIn int
Declare @IntToChar varchar(3)
Declare @IntToCharIn varchar(3)
set @loop = 1
while @loop <= @nCol
begin
set @IntToChar = convert(varchar(3), @loop)
--exec('select col'+ @IntToChar + ' from Table1')
exec('insert tableTmp(Col) select col'+ @IntToChar + ' from Table1')
--exec('select * from tableTmp')
set @loopIn = 1
while @loopIn <= @nRow
begin
set @IntToCharIn = convert(varchar(3), @loopIn)
if @loopIn = 1
exec('insert table2(col' + @IntToCharIn + ') select col from tableTmp where rownum = ' + @IntToCharIn)
else
exec('update table2 set col' + @IntToCharIn + ' = b.col from table2 a , tableTmp b where a.rownum = ' + @IntToChar + ' and b.rownum = ' + @IntToCharIn)
set @loopIn = @loopIn + 1
end
exec('truncate table tableTmp')
set @loop = @loop + 1
end
alter table Table2 drop column rownum
select * from Table1
select * from table2
-
I give you full 100 out of 100. Thank you.
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply