May 21, 2003 at 9:25 am
Hello everybody
i have a table with 100 rows ie
id
1
2
.
100
i want to break this one in one recordset with 3 fields thus
id1 id2 id3
1 35 69
2 36 70
3 37 71
4 38 72
5 39 73
. . .
. . .
34 68 null 102 null
if the recordset is not devided by 3 ( or x)equal, i will add a null row.
Can it be done without using a cursor?
if yes how?
Thanks.
May 22, 2003 at 2:56 am
If the ids are consecutive and want 3 cols then
declare @C int,@i int
select @i = count(*) from #t
set @C = ceiling(@i / 3.0)
select [id] as id1,
(case when [id]+@c > @i then null else [id]+@c end) as id2,
(case when [id]+@c+@c > @i then null else [id]+@c+@c end) as id3
from table
where [id] <= @C
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 4:06 am
Thanks for your reply
My problem is not only the id's but also the rest data. I need 4 fields one is the id the other is info1 the other is info2 and the other is info3. How can i use those fields.if i use it like :
cast(info1 as varchar(6) ) + ' ' + cast([id]+@c as varchar(5) ), i get the same result in all fields.
any suggestions?
May 22, 2003 at 4:34 am
Can u post table structure, small sample of data (non sensitive) and exactly what output you expect.
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 5:10 am
Table structure:
create table tblTempZips (zip Varchar(5) null, id int primary key IDENTITY (1,1) not null )
Sample Data:
insert into tblTempZips
(zip )
values
('12345')
insert into tblTempZips
(zip )
values
('23456')
insert into tblTempZips
(zip )
values
('23456')
insert into tblTempZips
(zip )
values
('23456')
insert into tblTempZips
(zip )
values
('23456')
insert into tblTempZips
(zip )
values
('12456')
insert into tblTempZips
(zip )
values
('12456')
insert into tblTempZips
(zip )
values
('12456')
insert into tblTempZips
(zip)
values
('33456')
insert into tblTempZips
(zip )
values
('22456')
insert into tblTempZips
(zip )
values
('44456')
insert into tblTempZips
(zip )
values
('55556')
insert into tblTempZips
(zip )
values
('55556')
insert into tblTempZips
(zip )
values
('55556')
insert into tblTempZips
(zip )
values
('55556')
insert into tblTempZips
(zip )
values
('55556')
insert into tblTempZips
(zip )
values
('55556')
when selecting data we have
12345
23456
23456
23456
23456
12456
12456
12456
33456
22456
44456
55556
so i want the zip field to split in 3 columns like
zip1 zip2 zip3
123452345633456
234561245622456
234561245644456
234561245655556
May 22, 2003 at 5:50 am
declare @C int
select @C = ceiling(count(*) / 3.0) from tblTempZips
select a.zip as zip1,b.zip as zip2,c.zip as zip3
from tblTempZips a
left outer join tblTempZips b on b.id = (a.id + @C)
left outer join tblTempZips c on c.id = (a.id + @C + @C)
where a.id <= @C
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 6:22 am
That one works fine.
Thanks a lot.
I also want to count the id column grouping by zip and then do the "split".
so we had
12345
23456
23456
23456
23456
12456
12456
12456
33456
22456
44456
55556
we make it
12345 1
23456 3
12456 3
33456 1
22456 1
44456 1
55556 1
and now we want to produce the previous conclusion:
zip1 zip2 zip3
12345 23456 33456
23456 12456 22456
23456 12456 44456
23456 12456 55556
May 22, 2003 at 6:47 am
Create another table from this one with distinct zip values and run the same query on the new table.
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 6:58 am
I have already done it but how will i add x times the zip code at the produced splitted columns?
May 22, 2003 at 7:21 am
create table tblTempZips2 (zip Varchar(15) null, id int primary key IDENTITY (1,1) not null )
insert into tblTempZips2 (zip)
select zip+' x '+cast(count(*) as varchar) from tblTempZips group by zip order by zip
declare @C int
select @C = ceiling(count(*) / 3.0) from tblTempZips2
select a.zip as zip1,b.zip as zip2,c.zip as zip3
from tblTempZips2 a
left outer join tblTempZips2 b on b.id = (a.id + @C)
left outer join tblTempZips2 c on c.id = (a.id + @C + @C)
where a.id <= @C
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 7:52 am
Maybe i confuse you
i create a table that have the followinf fields
Zip CountPerZip
12345 5
23456 4
23457 2
11111 1
22222 1
33333 1
Iwant to loop through the recodset and produce the following output.
12345 23456 23457
12345 23456 11111
12345 23456 22222
12345 23456 33333
12345 23457 null
May 22, 2003 at 8:06 am
create table #zipsum (zip Varchar(5) null,CountPerZip int)
insert into #zipsum values (12345, 5)
insert into #zipsum values (23456, 4)
insert into #zipsum values (23457, 2)
insert into #zipsum values (11111, 1)
insert into #zipsum values (22222, 1)
insert into #zipsum values (33333, 1)
create table #zipdet (zip Varchar(5) null)
declare @C int
set @C = 1
while (@c > 0)
begin
insert into #zipdet
select zip from #zipsum where CountPerZip > 0
update #zipsum set CountPerZip=CountPerZip-1 where CountPerZip > 0
select @C = count(*) from #zipsum where CountPerZip > 0
end
create table #zips (zip Varchar(5) null, id int primary key IDENTITY (1,1) not null )
insert into #zips (zip)
select zip from #zipdet order by zip
select @C = ceiling(count(*) / 3.0) from #zips
select a.zip as zip1,b.zip as zip2,c.zip as zip3
from #zips a
left outer join #zips b on b.id = (a.id + @C)
left outer join #zips c on c.id = (a.id + @C + @C)
where a.id <= @C
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2003 at 8:28 am
Thanks a lot.
That's exactly what i was looking for.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply