Disgregating a table.
-
HI.
In my payroll database, there's a table for the receipt's details, with this columns: rec_num, cod_con, tot_con where:
rec_num: number of the receipt
cod_con: is the code of the concept paided
tot_con: is the total amount of the concept.
The concepts are divided into asignations (the code begins with "A") and deductions (they begin with "R") i need to get a query with this columns:
rec_num: number of the receipt
cod_con_A: is the code of the ASIGNATION concept paided
tot_con_A: is the total amount of the ASIGNATION concept.
cod_con_R: is the code of the DEDUCTION concept paided
tot_con_R: is the total amount of the DEDUCTION concept.
Any suggestions?
Thanks a lot in advance.
-
I'm not quite clear what the table looks like or how you want your results, but assuming the table and it's data looks something like this;
create table #receipt
( rec_num int not null, cod_con char(5) not null, tot_con int not null )
insert #receipt select 123, 'A12', 100
insert #receipt select 123, 'R12', 50
insert #receipt select 123, 'A10', 250
insert #receipt select 123, 'A50', 125
select * from #receipt
rec_num cod_con tot_con
----------- ------- -----------
123 A12 100
123 R12 50
123 A10 250
123 A50 125
(4 row(s) affected)
Is this then what you're looking for?
select rec_num,
cod_con as cod_con_A,
tot_con as tot_con_A,
null as cod_con_R,
null as tot_con_R
from #receipt
where cod_con like 'A%'
UNION
select rec_num,
null as cod_con_A,
null as tot_con_A,
cod_con as cod_con_R,
tot_con as tot_con_R
from #receipt
where cod_con like 'R%'
rec_num cod_con_A tot_con_A cod_con_R tot_con_R
----------- --------- ----------- --------- -----------
123 NULL NULL R12 50
123 A10 250 NULL NULL
123 A12 100 NULL NULL
123 A50 125 NULL NULL
(4 row(s) affected)
/Kenneth
-
Nope..
What I'm looking for is:
rec_num cod_con_A tot_con_A cod_con_R tot_con_R ----------- --------- ----------- --------- ----------- 123 A10 250 R12 50 123 A12 100 NULL NULL 123 A50 125 NULL NULL
Note that the there are only 3 rocords, and the first record has data in asignations AND Deductions..
Thanks for your quick reply..
-
One solution which comes to my mind is that you can put the whole query inside a parent query & use order by clause.
-
Sorry.. I don't understand your suggestion...
Could you be more specific?
Thanks...
-
I was wondering whether something like this would solve your problem.
select * from
(
select rec_num,
cod_con as cod_con_A,
tot_con as tot_con_A,
null as cod_con_R,
null as tot_con_R
from #receipt
where cod_con like 'A%'
UNION
select rec_num,
null as cod_con_A,
null as tot_con_A,
cod_con as cod_con_R,
tot_con as tot_con_R
from #receipt
where cod_con like 'R%'
) order by cod_con_A, tot_con_A,cod_con_R,tot_con_R
-
Ohhh I am sorry. I think I got your requirement only now...
Let me think abt it... I will reply to you in a short while if I get some solution. :
-
I have one doubt... Does the rec_num value repeats ?
In our example, we have three ASIGNATION for the same receipt number. Is it allowed in the system ?
Just wanted to know this in order to think about the where condition of the query.
-
I think i have to say that I solved the problem with a little of programming.. but still i would like to optimize it with full SQL sentences if possible...
here is what i did.
1) Create a cursor for those concepts beginning with "A" called qa
2) Create a cursor for those concepts beginning with "R" called qr
3) Create an empty table called qf with fields:
rec_num, cod_con_A, tot_con_A, cod_con_R, tot_con_R
4) search each combintion of qa.rec_num+qa.cod_con in qf. if it doesn't exist, add the complete record.
5) search for those records which rec_num=qd.rec_num and cod_con_R=.null. if is found, replace the correspondinf fields, if not, add the complete record.
I hope this helps you to help me..
-
yes.. it repeats..
Imagine something like an invoice.. where the invoice number repeats for each item..
-
Hi Jonathan,
I think I found out a solution....Though its a long cut.
I couldnt find a way by which a simple SQL doing the trick. So I went for this long way... Plz let me know whether this works for you.
Declare @OutputString Varchar (500),
@rec_num int,
@A_cod_con VARCHAR (50),
@A_tot_con int,
@R_cod_con VARCHAR (50),
@R_tot_con int,
@A_Fetch_End int,
@R_Fetch_End int
declare cur_rec_num cursor
for
select distinct rec_num from #receipt
open cur_rec_num
fetch next from cur_rec_num into @rec_num
while @@fetch_status = 0
begin
declare cur_A_data cursor
for
select cod_con, tot_con from #receipt
where cod_con like 'A%'
and rec_num=@rec_num
declare cur_R_data cursor
for
select cod_con, tot_con from #receipt
where cod_con like 'R%'
and rec_num=@rec_num
open cur_A_data
open cur_R_data
SET @A_Fetch_End=0
SET @R_Fetch_End=0
while (@A_Fetch_End = 0 or @R_Fetch_End = 0)
begin
fetch next from cur_A_data into @A_cod_con,@A_tot_con
set @A_Fetch_End= @@fetch_status
fetch next from cur_R_data into @R_cod_con,@R_tot_con
set @R_Fetch_End= @@fetch_status
if (@A_Fetch_End = 0)
begin
set @OutputString = convert(varchar,@rec_num) + ',' + @A_cod_con + ',' + convert(varchar,@A_tot_con)
end
else
begin
set @OutputString = convert(varchar,@rec_num) + ',' + '' + ',' + ''
end
if (@R_Fetch_End=0)
begin
set @OutputString = @OutputString + ',' + @R_cod_con + ',' + convert(varchar,@R_tot_con)
end
else
begin
set @OutputString = @OutputString + ',' + '' + ',' + ''
end
print @OutputString
end
CLOSE cur_A_data
DEALLOCATE cur_A_data
CLOSE cur_R_data
DEALLOCATE cur_R_data
fetch next from cur_rec_num into @rec_num
end
CLOSE cur_rec_num
DEALLOCATE cur_rec_num
-
Oops... Now I saw your earlier posting saying that you too solved the problem by using cursors....
I was working on this program for abt 1 hour now & didnt notice your posting saying that you solved it.
Did you do the same way ?
-
Thanks a lot...
I'll give this a try.. and tell you later if it works. Thanks again.
-
Ah, I see... Well, I guess you're stuck with some 'ugly' iterative (ie cursor) process to build this result then. I don't belive it's possible to produce an output like this
rec_num cod_con_A tot_con_A cod_con_R tot_con_R
----------- --------- ----------- --------- -----------
123 A10 250 R12 50
123 A12 100 NULL NULL
123 A50 125 NULL NULL
from the four original rows within a SQL statement. The reason is that there are no relations between the 'A's and 'R's that shows this way. The above is a constructed display formatting (ie report).
The closest you can get with a single SQL statement is either
rec_num cod_con_A tot_con_A cod_con_R tot_con_R
----------- --------- ----------- --------- -----------
123 A12 100 R12 50
123 A10 250 R12 50
123 A50 125 R12 50
(3 row(s) affected)
or
rec_num cod_con_A tot_con_A cod_con_R tot_con_R
----------- --------- ----------- --------- -----------
123 NULL NULL R12 50
123 A10 250 NULL NULL
123 A12 100 NULL NULL
123 A50 125 NULL NULL
(4 row(s) affected)
..the output you want isn't supported by the tables design, therefore you must 'reconstruct' it by some iterative process like cursor or temtable... or reformat on the client side, if that is an option...
/Kenneth
-
Thanks a lot for your teaching reply.
Do we have in this forum the possibility of marking a reply as THE SOLUTION? as we have in universalthread.com?
If we can, let me know so i can place this mark on your great reply.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply