To create three rows against each row

[Mateen]

1st segment is data
2nd segment is result

for each row we need to create three rows with columns
(sno,acc_title,acc_code,debit,credit)

the three different type of code goes into acc_code
tot_amt goes into debit and credit is 0 (1st of the three rows)
company_contr goes into credit and debit is 0 (2nd of the three rows)
staff_contr goes into credit and debit is 0 (3rd of the three rows)
and so on for all rows maintaining a serial number counter.

I can do it with do while loop , but I need a set-based solution in Sql server 2008

create table pending (acc_title varchar(20) , tot_amt int, company_contr int, staff_contr int,bank_gl_code varchar(11)
,company_gl_code varchar(11), staff_gl_code varchar(11))

insert pending 
values('abc',500,250,250,'551-01','481-01','481-02'),
('xyz',200,100,100,'551-01','482-01','482-02'),
('pqr',400,200,200,'551-01','487-01','487-02')

[yosiasz]

is there any possibility that tomorrow there could be a brand new gl_code

bank_gl_code
company_gl_code
staff_gl_code
bankruptcy_gl_code
madoff_gl_code

etc?

[ScottPletcher]

I have no idea what "serial number counter":

and so on for all rows maintaining a serial number counter

nor "sno" refer to, so neither is included in this query.

SELECT p.acc_title, ca1.acc_code, ca1.debit, ca1.credit
FROM dbo.pending p
CROSS APPLY ( VALUES
    (1, bank_gl_code, tot_amt, 0),
    (2, company_gl_code, 0, company_contr),
    (3, staff_gl_code, 0, staff_contr)
) AS ca1(sort_seq, acc_code, debit, credit)
ORDER BY acc_title, sort_seq

[Mateen]

@yosiasz

is there any possibility that tomorrow there could be a brand new gl_code

No.

This is payment voucher in a provident fund system.
We are paying from a bank hence bank_gl_code
We are paying his contribution to pf (staff_gl_code)
and the employer contribution to pf (company_gl_code)

[ScottPletcher]

Of course at some point there could be a new code, it's always possible: a special fund, a retirement account, etc..

[Mateen]

<<I have no idea what "serial number counter">>

a running counter with increment 1.
Without this counter we cannot sort each group (set of 3 rows)
1st set of 3 rows for each acc_ttile will get counter 1,2,3
second set of 3 rows will get counter 4,5,6
the order of set (acc_title) is not so important.
.

Secondly your query is duplicating rows.:

[Mateen]

Of course at some point there could be a new code, it's always possible: a special fund, a retirement account, etc..

Yes indeed
Loan code, insurance code
but my current requirement is with above rigid code.

Actual events:
Due to some emergency we paid 50% advance and maintained an excel sheet.
Now we are creating payment voucher from that excel sheet.

[Mateen]

Currently i am doing this way

declare @result table (sno int,acc_title varchar(10), acc_code varchar(11),debit int, credit int)

declare @a int=0, @acc_title varchar(10)
set @acc_title=(select MIN(acc_title) from pending)
while @acc_title is not null
begin
set @a=@a+1
insert @result 
select @a,acc_title,bank_gl_code,tot_amt,0
from pending 
where acc_title = @acc_title
set @a=@a+1
insert @result 
select @a,acc_title,company_gl_code,0,company_contr
from pending 
where acc_title = @acc_title
set @a=@a+1
insert @result 
select @a,acc_title,staff_gl_code,0,staff_contr
from pending 
where acc_title = @acc_title

set @acc_title=(select MIN(acc_title) from pending where acc_title >@acc_title )
end
select * from @result

This is giving correct result but not set based.

[Mateen]

Sorry
Your answer is correct.

Since you are sorting on acc_title
I can add row number function to get my serial counter.

Thanks

[Mateen]

Hi all

A great way to learn is to ask expert in a forum like this.

I was doing silly way (looping).
Now I have learnt expert easy way.

[Mateen]

SELECT p.acc_title, ca1.acc_code, ca1.debit, ca1.credit
**, sno=ROW_NUMBER() over(order by acc_title,sort_seq)**
FROM dbo.pending p
CROSS APPLY ( VALUES
    (1, bank_gl_code, tot_amt, 0),
    (2, company_gl_code, 0, company_contr),
    (3, staff_gl_code, 0, staff_contr)
) AS ca1(sort_seq, acc_code, debit, credit)
ORDER BY acc_title, sort_seq

[Mateen]

@yosiasz

You can see that even if i am using ... (3 dots)
at the beginning and end of my sql code,
I am not getting the formatted sql.

Please guide.

[yosiasz]

hey @Mateen

not three dots .... but three ticks ```

[Mateen]

I cant seem to find back tick in my keyboard.

[yosiasz]

or just copy paste this?

this --> ```

[Mateen]

Finally got it.
Its below tilda

[yosiasz]

not tilda three ticks ```

[JeffModen]

@Mateen

Secondly your query is duplicating rows.:

If you're talking about Scott's code, I got no duplication of rows when I ran his code against your test data. Just the 3 sets of 3 each that you wanted.

If we make a simple mod to Scott's code, it does everything you want according to your original post. Here's the modification...

 SELECT  sno = ROW_NUMBER() OVER (ORDER BY p.acc_title, ca1.sort_seq)
        ,p.acc_title, ca1.acc_code, ca1.debit, ca1.credit
   FROM dbo.pending p
  CROSS APPLY 
        (VALUES
         (1, p.bank_gl_code   ,tot_amt, 0)
        ,(2, p.company_gl_code,0      , company_contr)
        ,(3, p.staff_gl_code  ,0      , staff_contr)
        ) ca1 (sort_seq, acc_code, debit, credit)
  ORDER BY sno
;

...and here are the results using the test data you provided in your original post...

@Mateen

A great way to learn is to ask expert in a forum like this.

I was doing silly way (looping).
Now I have learnt expert easy way.

The question now is... do you understand how the CROSS APPLY works to do the unpivoting and relational multiplication so you can do it again if you ever need to?

[ScottPletcher]

Thanks for the positive feedback. Sorry I originally misunderstood the requirement, it does seem rather obvious now. I was rather tired when I originally posted :-).

[Mateen]

@JeffModen
Scotpletcher code was ok.
I appologize in the some post back
I also figured out
sno = ROW_NUMBER() OVER (ORDER BY p.acc_title, ca1.sort_seq) post number 10

Thanks and regards.