Transpose rows to columns

Question

Transpose rows to columns

ramuvanparti

SSCrazy

Points: 2368
More actions
September 1, 2009 at 3:05 am

#204178

Hi all,
i have table with some row
select col from tab1
output
A
B
C
D
Now i have another table having four cols and want to insert above four individual row data into four different columns
selec col1,col2 , col3 ,col4 from tab2
output
A B C D
the data need to inserted from table tab1 only.
For this i have used cursor to insert data into four columns but there are some performance issue so want to void cursors and try some alternative solution.
Can any help in this regards
Ramu

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You have to be more specific then that. How do you decide which value goes to which column? Also post a small script that had the create table statements for both tables and insert statement for the first table. This will make answering your question easier to everyone.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

arun.sas SSChampion Points: 11831 More actions · Answer 2

Hi,

try this

create table TAB1

(

col char(2)

)

insert into TAB1

select 'A'

union all

select 'B'

union all

select 'C'

union all

select 'D'

create table TAB2

(

col1 char(2),

col2 char(2),

col3 char(2),

col4 char(2)

)

DECLARE @TAB1 VARCHAR(100),@SQL Nvarchar(500)

SELECT @TAB1 = COALESCE(@TAB1+''',''' , '') + col FROM TAB1

select @sql = N'insert into TAB2 values ('+''''+@TAB1+''''+')'

exec sp_executesql @sql

ramuvanparti SSCrazy Points: 2368 More actions · Answer 3

thanks for the reply , i have solved the problem.

I have concatenate all single row data to a variable with comma as follow

select @var = char(39)+colname+char(39)+','+@var from table

select @var = substring(@var,1,len(@var)-1) --to remove last comma char

and

exec(@var).

it solved my purpose thanks to all.