June 6, 2003 at 9:50 am
Hi all
i have the following question.
Can i split a recordset with one id and one field in 3 fields by calculating the count of records divided by three (ok till now) and produce the following
Using the ID Field
id id id
147
258
369
101417
111518
121619
202427
sample code
create table tblSample (id int primary key IDENTITY (1,1) not null,fld varchar(6))
insert into tblSample (fld) values ('10000') --id 1
insert into tblSample (fld) values ('10000') --id 2
insert into tblSample (fld) values ('11000') --id 3
insert into tblSample (fld) values ('11000') --id 4
insert into tblSample (fld) values ('11000') --id 5
insert into tblSample (fld) values ('12000') --id 6
insert into tblSample (fld) values ('12000') --id 7
insert into tblSample (fld) values ('13000') --id 8
insert into tblSample (fld) values ('14000') --id 9
insert into tblSample (fld) values ('24000') --id 10
insert into tblSample (fld) values ('25000') --id 11
insert into tblSample (fld) values ('27000') --id 12
insert into tblSample (fld) values ('29000') --id 13
insert into tblSample (fld) values ('30000') --id 14
insert into tblSample (fld) values ('31000') --id 15
declare @C int
select @C = ceiling(count(*) / 3.0) from tblSample
select isnull(a.fld,'') as fld1,
isnull(b.fld,'') as fld2,
isnull(c.fld,'') as fld3
from tblSample a
LEFT JOIN tblSample b on b.id = (a.id + 3)
LEFT JOIN tblSample c on c.id = (a.id + 6)
where a.id <= @C
order by fld1,fld2,fld3
Thanks in advance
June 6, 2003 at 10:45 am
I'm not sure if I understood your example, but I think your question
can be answered using this query
Select Count(1) As Rec_Cnt, fld, Count(1) / 3.0 As Div_Result
From tblsample
Group By fld
MW
MW
June 8, 2003 at 11:26 pm
Hi thanks for your respond.
I don't want to group by and count those fields i want the one field to become 3 by splitting the rows dividing by 3
In my example i have 15 rows so i want :
1)get the first record then get the forth and then the seventh for the first record
2) get the second the fifth and the eighth
3) get the third the sixth and the ninenth.
etc
id id id
1 4 7
2 5 8
3 6 9
10 14 null
11 15 null
June 9, 2003 at 12:09 am
Ok I solve it
Thanks.
November 4, 2016 at 9:03 am
Wrong post
November 4, 2016 at 10:46 am
DimitrisAgelidis (6/9/2003)
Ok I solve itThanks.
Good on you, care to share the solution?
😎
November 4, 2016 at 10:58 am
Eirikur Eiriksson (11/4/2016)
DimitrisAgelidis (6/9/2003)
Ok I solve itThanks.
Good on you, care to share the solution?
😎
From a post this old, my guess is that OP is long gone.
November 4, 2016 at 11:18 am
Ed Wagner (11/4/2016)
Eirikur Eiriksson (11/4/2016)
DimitrisAgelidis (6/9/2003)
Ok I solve itThanks.
Good on you, care to share the solution?
😎
From a post this old, my guess is that OP is long gone.
I must be getting old too:-D, didn't notice the date on the second last post
😎
Happy Friday Ed!
November 4, 2016 at 11:47 am
Eirikur Eiriksson (11/4/2016)
Ed Wagner (11/4/2016)
Eirikur Eiriksson (11/4/2016)
DimitrisAgelidis (6/9/2003)
Ok I solve itThanks.
Good on you, care to share the solution?
😎
From a post this old, my guess is that OP is long gone.
I must be getting old too:-D, didn't notice the date on the second last post
😎
Happy Friday Ed!
We all are, Eirikur. Happy Friday to you too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply