April 11, 2008 at 11:46 am
i have a table like
table1
aa bb cc dd
1 2 3 4
5 6 7 8
i need to convert columns in to rows...............( to use in reporting)
table2
aa 1 5
bb 2 6
cc 3 7
dd 4 8
and the number of columns/rows are not fixed..........as this table1 is present in cursor.......for each iteration the number of columns may change........................
thanks for ur help in advance
April 11, 2008 at 12:43 pm
Can you provide more information (column names, etc.)?
Generally, you'll pivot over a key field value (for instance, how do you know that value 1 goes in column 1, not column 2?). You should provide more structure information and we'll be able to help you more...
April 11, 2008 at 12:55 pm
Columns names are aa,bb,cc,dd
in the prev post
for Example.............
address phonenum code
plano 555-555-55555 1234
dallas 111-111-1111 4321
any thing else needed...
Select @selectQuery = 'Select +right(@Columns,len(@columns)-1)
' From ' + @testname +
'Where' +' (' +Right(@WhereClause, Len(@WhereClause) - 4)+')
@columns contain column names(dynamic a,b,c)
@testname table name
the O/p table is present in @Select Query
execute @Select Query
address phonenum code
[/b]
plano 555-555-55555 1234
dallas 111-111-1111 4321
thanks for ur response
April 14, 2008 at 8:20 am
My question was more about the table2 structure, and what you're expecting to see.
Either way, you're not talking about a PIVOT as much as a full transpose. There's probably some good algorithms on transposing an array out there, but off the top of my head:
Assume this for table1:
create table mytable
(
id int,
address varchar(100),
phone varchar(10),
other_info varchar(100)
)
insert into mytable ( id, address, phone, other_info ) values ( 1234, 'plano', '555-555-55555', 'other stuff' )
insert into mytable ( id, address, phone, other_info ) values ( 4321, 'dallas', '111-111-1111', 'more other stuff' )
If column order doesn't matter, one way to go is something like this:
make sure your table doesn't have more than 1023 rows.
if ( select count(*) from [mytable] ) > 1023
raiserror(...)
Build a SELECT to build each column for a given field. Loop over all fields to get all values.
declare @SQLString varchar(max)
declare @SQLString_thiscol varchar(max)
set @SQLString = ''
DECLARE unpivotthiscol_cursor CURSOR
READ_ONLY
FOR
select column_name
from utilities.information_schema.columns
where table_name = 'mytable' and column_name not in ('id')
DECLARE @columnName varchar(128)
OPEN unpivotthiscol_cursor
FETCH NEXT FROM unpivotthiscol_cursor INTO @columnName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @SQLString_thiscol = ' select ''' + @columnName + ''' as unpivotted_field'
select @SQLString_thiscol = @SQLString_thiscol +
',MAX(case when id = ' + cast(id as varchar(11)) + ' then cast([' + @columnName + '] as varchar(1000)) else null end) as [' + cast(id as varchar(11)) + ']'
from mytable
set @SQLString_thiscol = @SQLString_thiscol + ' from mytable union all'
set @SQLString = @SQLString + @SQLString_thiscol
END
FETCH NEXT FROM unpivotthiscol_cursor INTO @columnName
END
CLOSE unpivotthiscol_cursor
DEALLOCATE unpivotthiscol_cursor
set @SQLString = left(@SQLString, len(@SQLString) - len('union all'))
print @SQLString
exec (@SQLString)
There's definitely room for improvement here, but I haven't really sat down and thought about the best way to go (I'm actually thinking CLR). I tested this with 100 columns x 100 rows, and it runs in 10 seconds. But for a more typical, 10x10, it's under 1 second. So this should do you.
April 14, 2008 at 8:27 am
thankyou ................i will try with this.................
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply