March 16, 2007 at 2:35 am
how to split select result to dynamic field
i explain
i have table like this
sn | fld |
1 | 3 |
1 | 55 |
1 | 77 |
2 | 89 |
3 | 4 |
3 | 6 |
3 | 8 |
3 | 9 |
3 | 45 |
3 | 77 |
4 | 54 |
4 | 23 |
5 | 109 |
--------------------
and i want to show it like this
-------------
sn | fld1 | fld2 | fld3 | fld4 | fld5 | fld6 |
1 | 3 | 55 | 77 | |||
2 | 89 | |||||
3 | 4 | 6 | 8 | 9 | 77 | |
4 | 54 | 23 | ||||
5 | 109 |
--------------------
TNX
March 16, 2007 at 3:47 am
There are about a thousand posts about this on this forum. Please do a search on "crosstab" and "pivot".
--Jeff Moden
March 16, 2007 at 7:36 am
Do it using pivot option that is available. it sure gives a solution to your req.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 16, 2007 at 4:46 pm
The "Pivot option" you speak of is not available in SQL Server 2000... and this IS an SQL Server 7/2000 forum.
--Jeff Moden
March 19, 2007 at 7:51 am
March 19, 2007 at 2:16 pm
I didn't handle nulls in your output, but I got you as far as that problem...
/*building my input*/
create table myInput (sn int, fld int)
insert into myInput values (1,3)
insert into myInput values (1,55)
insert into myInput values (1,77)
insert into myInput values (2,89)
insert into myInput values (3,4)
insert into myInput values (3,6)
insert into myInput values (3,8)
insert into myInput values (3,9)
insert into myInput values (3,45)
insert into myInput values (3,77)
insert into myInput values (4,54)
insert into myInput values (4,23)
insert into myInput values (5,109)
--need a couple variables
declare @maxWidth int,
@thisWidth int,
@sql nvarchar(4000)
--how wide are we building the Output table?
set @maxWidth =
(select top 1 count(*) from myInput
group by sn
order by count(*) desc)
/*
Build your mystery-length table to hold onto the output. I'm not sure how to do this without creating a physical table in the database that you'll have to drop at the end of the script, but this will get you where you want to go
*/
if @maxWidth > 0
begin
set @sql = 'Create table myOutput (sn int'
set @thisWidth = 1
while @thisWidth <= @maxWidth
begin
set @sql = @sql + ', fld' + cast(@thisWidth as varchar(10)) + ' int'
set @thisWidth = @thisWidth + 1
end
set @sql = @sql + ')'
exec sp_executesql @sql
end
GO /*I put this here because it's difficult to reference later in the script if it doesn't already exist. Note I had to recreate the @sql variable to accomodate the GO just a few lines down.*/
/*
Populate your mystery table. I hate cursors, but this is what I've got
*/
declare @SN int,
@fld int,
@colCounter int,
@sql nvarchar(4000)
declare snCursor cursor fast_forward for
select distinct sn from myInput
order by sn
open snCursor
fetch next from snCursor into @SN
while @@fetch_status = 0
begin
--create your new row
insert into myOutput (sn) values (@sn)
set @colCounter = 1
--go get your fld values
declare fldCursor cursor fast_forward for
select /*distinct ?*/ fld from myInput
where sn = @SN
order by fld
--update one field at a time. haven't thought of a better way yet
open fldCursor
fetch next from fldCursor into @fld
while @@fetch_status = 0
begin
set @sql = 'update myOutput set fld' + cast(@colCounter as varchar(3)) + ' = ' + cast(@fld as varchar(10)) + ' where sn = ' + cast(@sn as varchar(10))
exec(@sql)
set @colCounter = @colCounter + 1
fetch next from fldCursor into @fld
end
close fldCursor
deallocate fldCursor
fetch next from snCursor into @SN
end
close snCursor
deallocate snCursor
--pull output
select * from myOutput
-- drop tables
drop table myInput
drop table myOutput
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply