Expression to Parameter

I would like my header text not to be hard coded

I took a very big table which include texts i need from my DB to DS

How can i pick a single row with single data in it to be bind

to my Paramter ?

Could you explain this in more detail ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Expressing a One to Many Relationship in a Single Row

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.

exporting to pdf

When we export a report as a PDF and if there are multiple columns in the
report, all the columns in the report do not fit in one single page in the
exported PDF. We would ideally like to accommodate all the columns within one
single page. The only way we could do this today is to reduce the width of
the columns , but this causes the Report to loose its clarity and readability.
Thanks in advance,Go to the report menu item and open "report properties". On the "layout"
tab, increase the "page width" property accordingly. Re-deploy the report,
run and export.
"Chandra" wrote:
> When we export a report as a PDF and if there are multiple columns in the
> report, all the columns in the report do not fit in one single page in the
> exported PDF. We would ideally like to accommodate all the columns within one
> single page. The only way we could do this today is to reduce the width of
> the columns , but this causes the Report to loose its clarity and readability.
> Thanks in advance,
>

Exporting to multiple files from a single source table using DTS

Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.Can't you split up your export by using views with restrictions on your recordtype instead of your table?|||manthenasri, does DTS allow you to redirect output on the fly? From what I have seen the source and destination is fixed at design time.

Have you tried BCP with a query? You could use DTS to script your BCP commands to a CMD file. Then at the cmd prompt you could use DTSRUN to build the CMD file and then execute the CMD file. Or just set up a procedure to script the BCP commands and use xp_cmdshell.

Sort of bassackwards but it's the best I could come up with.|||Hello Paul

I do not know much about BCP. But here is the problem that i have to solve.

I have a table by name T1 which has the following data

Business Unit Name SSN RATE
1 Joe 923456789 40
1 Jenny 568234569 50
3 Mike 234198634 35
3 Meri 743579374 45

Now i need to create a two files by name 1.ben and 3.ben on the fly.
and them put in the data related to Joe and Jenny into 1.ben and the data for Mike and Meri into 3.ben. If there is more data related to other business units i need to create those files also.

Can you please tell me how i can accomplish using BCP.

Thanks,
Srinivas.|||I think the BCP command you want is...

bcp "select * from <dbname>.<owner>.<table> where <where clause>" queryout <file name> -c -T -S <server>

probably could wrap it all up like this:
create table manthenasri([Business Unit] int, [Name] varchar(15), SSN varchar(9), Rate int)
insert into manthenasri values(1,'Joe','923456789',40)
insert into manthenasri values(1,'Jenny','568234569',50)
insert into manthenasri values(3,'Mike','234198634',35)
insert into manthenasri values(3,'Meri','743579374',45)

declare @.bu int, @.TSQL varchar(255)
select @.bu = min([Business Unit]) from manthenasri
while @.bu is not null begin
set @.TSQL = 'bcp "select * from ' + db_name() + '..' + 'manthenasri where [Business Unit] = ' + cast(@.bu as varchar) + '" queryout ' + cast(@.bu as varchar) + '.ben -c -T -S ' + @.@.servername
print @.TSQL
-- master.dbo.xp_cmdshell(@.TSQL)
select @.bu = min([Business Unit]) from manthenasri where [Business Unit] > @.bu
end

Open Query Analyzer and run the above code. It should produce a few BCP commands. Make adjustments for your table and rerun the script. Next try cutting and pasting the results to a CMD prompt and see how it works. Once that's done you can un-comment the xp_cmdshell call and try running this on your server.

THIS IS UNTESTED CODE!

Look this over and post back with questions.|||Hi Srinivas ,

Use DTS designer add one data pump task for each condition and output file.

For example : Use select statement as the source of data pump task with where clause and desination to the the disired out put file.

cheers :)

Shaji

Originally posted by manthenasri
Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.|||Hai Paul


Thank you for the BCP solution that you have given. I really appreciate you patience in giving me a sample program also.

The sample worked just fine except for the brackets enclosing the variable @.Tsql. I think these brackets should not be there. I had to remove these brackets.

But now have a problem of reading from multiple files and posting that data into table. I have to do an isert and also an update on the table. How can i do that?

Thank you very much once again.

Srinivas.