I've two tables.
Table Charge
Table Payments
Charge has a one to many relationship with payments.
I have to transform the data from this new system of normalized tables over to a text file so that it may be read into an old mainframe computer. For each record I need to include the charge details and up to 10 payment details all on the same row.
Like this:
Charge1, ChargeDate, ChargeAmount, Payment1Amount, Payment2Amount, Payment3Amount
Charge2, ChargeDate, ChargeAmount, Payment1Amount, Payment2Amount, Payment3Amount
etc.
If their are less than 10 payments, the row has to be padded.
All of this is going to be DTS'd to a text file.
Is there a query that can handle this?
Simple answer yes:
Without seeing the table structures I can write some code based on these assumptions:
1) when you say Charge1, Charge2 that this is an ID field so I named it ChargeID
2) The Payments table carries the fieldname Payment.
3) Payments table carries the ChargeID as well for reference.
4) Payments carries the payment# represented here as paymentnum
Code Snippet
select distinct chargeID, ChargeDate, ChargeAmount
, pay1.payment [Payment1Amount]
, pay2.payment [Payment2Amount]
, pay3.payment [Payment3Amount]
, pay4.payment [Payment4Amount]
, pay5.payment [Payment5Amount]
, pay6.payment [Payment6Amount]
, pay7.payment [Payment7Amount]
, pay8.payment [Payment8Amount]
, pay9.payment [Payment9Amount]
, pay10.payment [Payment10Amount]
from charge
left join payments [pay1] on pay1.ChargeID = charge.ChargeID and pay1.paymentnum = 1
left join payments [pay2] on pay2.ChargeID = charge.ChargeID and pay2.paymentnum = 2
left join payments [pay3] on pay3.ChargeID = charge.ChargeID and pay3.paymentnum = 3
left join payments [pay4] on pay4.ChargeID = charge.ChargeID and pay4.paymentnum = 4
left join payments [pay5] on pay5.ChargeID = charge.ChargeID and pay5.paymentnum = 5
left join payments [pay6] on pay6.ChargeID = charge.ChargeID and pay6.paymentnum = 6
left join payments [pay7] on pay7.ChargeID = charge.ChargeID and pay7.paymentnum = 7
left join payments [pay8] on pay8.ChargeID = charge.ChargeID and pay8.paymentnum = 8
left join payments [pay9] on pay9.ChargeID = charge.ChargeID and pay9.paymentnum = 9
left join payments [pay10] on pay10.ChargeID = charge.ChargeID and pay10.paymentnum = 10
You can modify this to match your field names. If you don't have a simple paymentnum field, and they need to order by date or something, then I'll or somebody else will have to think a little longer.
right now the empty rows will come out NULL.
However you can represent them as blank or 'no payment' by this:
Code Snippet
case when pay10.payment is null then 'no payment' else pay10.payment end [Payment10Amount]
|||
You could use CTE + row_number() function for with task:
Code Snippet
createtable Charge
(
Id int,
ChargeDate datetime,
ChargeAmount decimal
)
createtable Payments
(
ChargeID int,
PaymentAmount decimal
)
insertinto Charge values(1,'2007-01-01', 1000)
insertinto Payments values(1,10)
insertinto Payments values(1,1)
insertinto Payments values(1,13)
insertinto Payments values(1,12)
insertinto Payments values(1,14)
insertinto Payments values(1,15)
insertinto Payments values(1,17)
insertinto Payments values(1,18)
insertinto Payments values(1,19)
insertinto Charge values(2,'2007-02-01', 100)
insertinto Payments values(2,37)
insertinto Payments values(2,38)
insertinto Payments values(2,49)
with CTE
as
(
--Numerate Payments for each ChargeID
select ChargeID , p.PaymentAmount,
row_number()over(Partitionby ChargeID Orderby ChargeID )as num
from Payments p
)
select
Id, ChargeDate, ChargeAmount
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=1)as PaymentAmount1
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=2)as PaymentAmount2
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=3)as PaymentAmount3
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=4)as PaymentAmount4
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=5)as PaymentAmount5
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=6)as PaymentAmount6
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=7)as PaymentAmount7
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=8)as PaymentAmount8
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=8)as PaymentAmount9
,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=10)as PaymentAmount10
from Charge
Also check output of following query for understanding:
Code Snippet
select ChargeID , p.PaymentAmount,
row_number()over(Partitionby ChargeID Orderby ChargeID )as num
from Payments p
|||
Robert,
Thanks for trying, but the payment table could return up to 10 separate rows.
I've found that the answer seems to be temporary tables and cursors.
Once I get an answer, I'll post it here.
|||
I'm not sure what you mean here I realize that in the Payment table there will be 10 seperate rows. Hence the joining 10 different times the same table.
The structure that we are nomalizing is this correct?
Charge
charge1 amount, payment
Then in the payment table:
Charge1 payment1 $20
Charge1 payment2 $20
Charge1 payment3 $30
... and so on up to 10 times...
the above code will work for this... It is in fact built to handle up to 10 rows in the payment table for one charge.
BTW I was editing when you replied, I added on the section below about changing nulls to 'no payment' and other things you can do with the fields returned.
|||
You can use the following query and simply export it as text file,
Code Snippet
Create Table #charge (
[cid] int ,
[cname] Varchar(100) ,
[cdate] datetime
);
Insert Into #charge Values('1','Charge1','1/1/2007');
Insert Into #charge Values('2','Charge2','1/2/2007');
Insert Into #charge Values('3','Charge3','1/3/2007');
Create Table #payment (
[pid] int ,
[cid] int ,
[pamount] money
);
Insert Into #payment Values('1','1','1000');
Insert Into #payment Values('2','1','10000');
Insert Into #payment Values('3','1','100');
Insert Into #payment Values('4','1','6000');
Insert Into #payment Values('5','1','9000');
Insert Into #payment Values('6','1','100');
Insert Into #payment Values('7','1','100000');
Insert Into #payment Values('8','1','10098');
Insert Into #payment Values('9','1','1000');
Insert Into #payment Values('10','1','1029');
Insert Into #payment Values('16','1','34533');
Insert Into #payment Values('11','2','1000000');
Insert Into #payment Values('12','2','1928');
Insert Into #payment Values('13','2','100');
Insert Into #payment Values('14','2','10000');
Insert Into #payment Values('15','3','100');
Code Snippet
--SQL Server 2000
Select C.Cid,Cname,cdate,pamount into #Temp from #charge C Join #payment P on C.Cid=P.Cid
--Adding the Row Number, Grouped Row Number
Alter table #Temp Add RowId Int Identity(1,1), GroupId int, RowGroup Int
--Finding the Grouped Row Number
Update #Temp
Set
GroupId = RowId - (Select Min(RowID)-1 From #Temp Sub Where #Temp.Cid = Sub.Cid);
--Finding the Row Group
Update #Temp
Set RowGroup = (GroupId-1)/10
--Refine the Grouped Row Number
Update #Temp
Set GroupId = GroupId - (Select Min(GroupId)-1 From #Temp Sub Where #Temp.Cid = Sub.Cid And #Temp.RowGroup = Sub.RowGroup)
--Display the Conetent
Select
RowGroup+1 RowNumber
,CName
,Cdate
,Isnull(Cast(Max(Case When GroupId=1 Then pamount End) as Varchar),'') Payement1Amount
,Isnull(Cast(Max(Case When GroupId=2 Then pamount End) as Varchar),'') Payement2Amount
,Isnull(Cast(Max(Case When GroupId=3 Then pamount End) as Varchar),'') Payement3Amount
,Isnull(Cast(Max(Case When GroupId=4 Then pamount End) as Varchar),'') Payement4Amount
,Isnull(Cast(Max(Case When GroupId=5 Then pamount End) as Varchar),'') Payement5Amount
,Isnull(Cast(Max(Case When GroupId=6 Then pamount End) as Varchar),'') Payement6Amount
,Isnull(Cast(Max(Case When GroupId=7 Then pamount End) as Varchar),'') Payement7Amount
,Isnull(Cast(Max(Case When GroupId=8 Then pamount End) as Varchar),'') Payement8Amount
,Isnull(Cast(Max(Case When GroupId=9 Then pamount End) as Varchar),'') Payement9Amount
,Isnull(Cast(Max(Case When GroupId=10 Then pamount End) as Varchar),'') Payement10Amount
from
#Temp
Group By
CId
,CName
,Cdate
,RowGroup
Code Snippet
-SQL Server 2005
;With Ordered
as
(
Select C.Cid,Cname,cdate,pamount,Row_Number() OVER (Partition By C.Cid Order By C.Cid) GroupRowId from #charge C Join #payment P on C.Cid=P.Cid
)
, Grouped
as
(
Select Cid,Cname,Cdate,pamount,(GroupRowId-1)/10 RowGroup From Ordered
)
, ReGrouped
as
(
Select Cid,Cname,Cdate,pamount,RowGroup,Row_Number() Over(Partition By RowGroup Order By RowGroup) GroupRowId From Grouped
)
Select
RowGroup + 1 RowNumber
,CName
,CDate
,Isnull(Cast([1] as Varchar),'') Payment1Amount
,Isnull(Cast([2] as Varchar),'') Payment2Amount
,Isnull(Cast([3] as Varchar),'') Payment3Amount
,Isnull(Cast([4] as Varchar),'') Payment4Amount
,Isnull(Cast([5] as Varchar),'') Payment5Amount
,Isnull(Cast([6] as Varchar),'') Payment6Amount
,Isnull(Cast([7] as Varchar),'') Payment7Amount
,Isnull(Cast([8] as Varchar),'') Payment8Amount
,Isnull(Cast([9] as Varchar),'') Payment9Amount
,Isnull(Cast([10] as Varchar),'') Payment10Amount
From
ReGrouped
PIVOT
(
Max(pamount) For GroupRowId In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as Pvt
|||
WOW.
Thanks for the options guys.
I'll be looking into each one of these.
