Combining Values

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

And combine them into this:

John Doe | Address 1; Address 2; Address 3

Do I need a cursor for this?imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
And combine them into this:
>
John Doe | Address 1; Address 2; Address 3
>
>
Do I need a cursor for this?

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.|||I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Do I need a cursor for this?

>
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
- Show quoted text -

|||If you cant do this in reports, refer this

http://sqljunkies.com/WebLog/amacha...px?Pending=true
Madhivanan

On Jun 30, 10:47 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Do I need a cursor for this?

>

Quote:

Originally Posted by

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

You have three options:

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

Option 1) is the safest method.

For option 2) details refer to:
http://groups.google.pl/group/micro...cab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info|||Here's where things get interesting:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

The results need to be

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

I have no idea how to pull this off.

On Jun 30, 6:39 am, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>
You have three options:
>
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Option 1) is the safest method.
>
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info

|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
>
The results need to be
>
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
>
I have no idea how to pull this off.
>
On Jun 30, 6:39 am, "Marcin A. Guzowski"
>
>
>
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info- Hide quoted text -

>
- Show quoted text -

You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.|||Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?

On Jul 2, 3:28 am, Stephen2 <Step...@.mailinator.comwrote:

Quote:

Originally Posted by

On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
>
>
>
>
>
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

>

Quote:

Originally Posted by

The results need to be

>

Quote:

Originally Posted by

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

>

Quote:

Originally Posted by

I have no idea how to pull this off.

>

Quote:

Originally Posted by

On Jun 30, 6:39 am, "Marcin A. Guzowski"

>

Quote:

Originally Posted by

<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info-Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.- Hide quoted text -
>
- Show quoted text -

sqlsql

Combining the content of two tables

Hi all,

How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks

Table A

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Table B

Client Weight Purchase

Jim 2 5

Lee 4 3

Bob 6 7

Combination table (result)

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Jim 2 5

Lee 4 3

Bob 6 7

Give a look to the UNION and UNION ALL operators in books online. It should look something like this:

Code Snippet

select client,
weight,
purchase
from [table a]
union all -- or perhaps union
select client,
weight,
purchase
from [table b]

|||Works perfectly. Thanks.sqlsql

Combining Reports

am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotHi, Paul
I think we'd need more information about your application/UI and method
of delivery...
but making some simple assumption, if you are dealing with your own
custom web app, you can use the SOAP api to invoke reporting services
and render the reports you need.
A nice way to present this would be to use the fileshare delivery
extension, and have the ReportServer render the selected reports to
this share, then just send an email or notification to the user with a
link to access the file share where the PDFs have been saved.
You can also create one big report, that has multiple datasets and
several dataregions, corresponding to different "reports." Then using
parameters, you can drive which datasets get to be populated with data
or hidden from the user...Note that this approach may be a bit slower,
but it will provide a better way of giving the user one single PDF
which contains multiple "reports."
I hope I've given you some ideas to get started.
Regards,
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot|||"tafs7" <tsilva7@.gmail.com> wrote in message
news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
Thiago,
Firstly, Great Name.
Right here is the setup / Architecture, the company I am currently working
for has been using SSRS2000 to provide reports to clients via a web
interface allowing them a series of criteria to make their bespoke report.
All information is saved to a database and a GUID and Version ID is supplied
back to the interface that will then be passed to a process filter that will
then be sent off to SSRS as parameters to be disseminated to the Stored
procedure that is called from the RDL. Hey presto, SSRS send back the
desired report in the desired format.
Now, my lead developer has said, Paul you are a Genius!!!, we need you to
provide a solution for our clients to say I would like "Report 1", "Report
4" and "Report 12" [Where each of these reports is an individual report RDL
Template] to be be selected so as to be combined into one single but
brilliant report. He has also asked as a request that it has continuous
Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
s generated and only containing the description to Icons that are contained.
Now this I know is a massive job to undertake and I have a couple of Ideas
of how it can be done. However if there is a way that I can say, "Right I
have a generated report for each individual report, 'Report 1', 'Report 4'
and 'Report 12' and then say go and get me these GENERATED reports and
output them into a single PDF or Excel.
I am guessing that this would be a bigger issue than your suggestion and it
is not something I would get SSRS to do.
Thank you for you reply and look forward to hearing a response.
Paul
> Hi, Paul
> I think we'd need more information about your application/UI and method
> of delivery...
> but making some simple assumption, if you are dealing with your own
> custom web app, you can use the SOAP api to invoke reporting services
> and render the reports you need.
> A nice way to present this would be to use the fileshare delivery
> extension, and have the ReportServer render the selected reports to
> this share, then just send an email or notification to the user with a
> link to access the file share where the PDFs have been saved.
> You can also create one big report, that has multiple datasets and
> several dataregions, corresponding to different "reports." Then using
> parameters, you can drive which datasets get to be populated with data
> or hidden from the user...Note that this approach may be a bit slower,
> but it will provide a better way of giving the user one single PDF
> which contains multiple "reports."
> I hope I've given you some ideas to get started.
> Regards,
> Thiago Silva
> MCAD.NET
> Paul Roberts wrote:
>> am currently looking for a way to potentially combine several types of
>> reports systematically to create a single report pack for a client.
>> This would need to be done on the fly so the clients can choose which
>> bits /
>> Reports they wish to have and then click the Download PDF button and hey
>> presto here it comes?
>> Is there a way and if so can anyone let me know the best / easiest way
>> for a
>> simple brain to do it!
>> Cheers alot
>|||Paul,
Firstly, thanks for the name compliment...it's Portuguese (BR), if
you're wondering.
I don't think it would be easy or even possible to generate separate
reports (different RDLs), then combine them into PDF via code, etc.
The best approach to this in my opinion, still would be to have one RDL
that contains the different report bodies in their contained rectangles
or tables or lists, and based on parameters for which report number was
selected, only execute the appropriate query and render the appropriate
RDL body/data section.
Unfortunately, RS does not allow for expression in Subreport names,
otherwise, I would recommend an entry point report with a Subreport
that would be defined based on a parameter, contained in a table. Then
you could write a little SQL to parse the entered rpt numbers as
individual rows, so you'd have the Subreport render different report
numbers in each "row" of the parent table/list. Hope this makes sense,
but it won't matter 'cause it ain't supported ;-)
Anyways, that's my 2 cents.
Cheers back at you!
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> "tafs7" <tsilva7@.gmail.com> wrote in message
> news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
> Thiago,
> Firstly, Great Name.
> Right here is the setup / Architecture, the company I am currently working
> for has been using SSRS2000 to provide reports to clients via a web
> interface allowing them a series of criteria to make their bespoke report.
> All information is saved to a database and a GUID and Version ID is supplied
> back to the interface that will then be passed to a process filter that will
> then be sent off to SSRS as parameters to be disseminated to the Stored
> procedure that is called from the RDL. Hey presto, SSRS send back the
> desired report in the desired format.
> Now, my lead developer has said, Paul you are a Genius!!!, we need you to
> provide a solution for our clients to say I would like "Report 1", "Report
> 4" and "Report 12" [Where each of these reports is an individual report RDL
> Template] to be be selected so as to be combined into one single but
> brilliant report. He has also asked as a request that it has continuous
> Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
> s generated and only containing the description to Icons that are contained.
> Now this I know is a massive job to undertake and I have a couple of Ideas
> of how it can be done. However if there is a way that I can say, "Right I
> have a generated report for each individual report, 'Report 1', 'Report 4'
> and 'Report 12' and then say go and get me these GENERATED reports and
> output them into a single PDF or Excel.
> I am guessing that this would be a bigger issue than your suggestion and it
> is not something I would get SSRS to do.
> Thank you for you reply and look forward to hearing a response.
>
> Paul
>
> > Hi, Paul
> >
> > I think we'd need more information about your application/UI and method
> > of delivery...
> >
> > but making some simple assumption, if you are dealing with your own
> > custom web app, you can use the SOAP api to invoke reporting services
> > and render the reports you need.
> >
> > A nice way to present this would be to use the fileshare delivery
> > extension, and have the ReportServer render the selected reports to
> > this share, then just send an email or notification to the user with a
> > link to access the file share where the PDFs have been saved.
> >
> > You can also create one big report, that has multiple datasets and
> > several dataregions, corresponding to different "reports." Then using
> > parameters, you can drive which datasets get to be populated with data
> > or hidden from the user...Note that this approach may be a bit slower,
> > but it will provide a better way of giving the user one single PDF
> > which contains multiple "reports."
> >
> > I hope I've given you some ideas to get started.
> >
> > Regards,
> > Thiago Silva
> > MCAD.NET
> >
> > Paul Roberts wrote:
> >> am currently looking for a way to potentially combine several types of
> >> reports systematically to create a single report pack for a client.
> >>
> >> This would need to be done on the fly so the clients can choose which
> >> bits /
> >> Reports they wish to have and then click the Download PDF button and hey
> >> presto here it comes?
> >>
> >> Is there a way and if so can anyone let me know the best / easiest way
> >> for a
> >> simple brain to do it!
> >>
> >> Cheers alot
> >

Combining Reports

I am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotIf I understood correctly, you want a list of reports in one page and when
the client clicks on any report or download option it should download.
You can use "Action" to create a page with all of your reports and use
action to render or create a small program using asp.net and use render
method and create pdf depending on the report clicks.
Amarnath
"Paul Roberts" wrote:
> I am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot
>
>

columns not appearing.

I have generated a .dsv using named queries.

The dsv consists of 3 tables:

1)CLIENT

2)client_guarantor.

3)client_contacts.

The client_guarantor and client_contacts tables both have a foreign key into the CLIENT table via the column CLIENT.client_id. To give you a visual, the .dsv design view would look something like this:

client_guarantor <- CLIENT -> client_contacts.

I use this .dsv to generate a model and deploy it. My problem arises once I get into report builder. It will not let me build a report that uses columns from all three tables. For instance, if i pull a column from the CLIENT table, then a column from the client_contacts table, and next try to get anything from the client_guarantor table, report builder lets me navigate back to the client_guarantor table (because i have added a column from CLIENT) but wont let me add any columns from client_guarantor to the report.

Am i missing something? I assume that since the CLIENT table can see both the client_guarantor and client_contacts tables, if I add anything from the CLIENT table into my report, I should have access to both other tables located in my model. Am I wrong in this assumption, or do I need to add some flag to my model or something? Any help or insight is appreciated.

I searched through the forum, and for anyone interested, the answer is here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=95688&SiteID=1

columns in select list

In your client, sure -- every client library can do that. In SQL Server, probably not. What is the business or technical problem you're trying to solve? Perhaps we can come up with a better way of handling it. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <buragohain@.discussions.microsoft.com> wrote in message news:6926c563-86b6-40a5-a3f0-3bc32f8e9e94@.discussions.microsoft.com... hi friends!Suppose, i have a query:SELECT fname AS Firstname, lname AS Lastname FROM CustomerNow is it possible to get the column names produced by the above query? LikeFirstnameLastnamejust like we extract column names from information_schema.columns for a table name?with regards,Buragohain

hi friends!

Suppose, i have a query:

SELECT fname AS Firstname, lname AS Lastname FROM Customer

Now is it possible to get the column names produced by the above query? Like

Firstname
Lastname

just like we extract column names from information_schema.columns for a table name?

with regards,
Buragohain

|||What are you consuming it with? ADO with vb? ado.net ?
|||Hi friends!
Thanx everyone for your response.

By creating a temporary table using the query, then accessing the columns, and then deleting the temporary table, will solve the problem. But i wonder is there any in-built technique is Sql server for that or not. Like:

exec sp_columns 'Customer'

exec sp_columns 'select * from Customer' (wrong)

exec sp_columns 'SELECT fname AS Firstname, lname AS Lastname FROM Customer' (wrong)

Again, if there is any technique in ADO.NET, then it will be helpfull too.

(my problem was to show friendly column names to end user from several tables joined.)

with regards,

Colspan in reports?

Is it possible to manipulate table or matrix cells with colspan in reports?
I have several reports that show Client and Project.
Currently, the only way I have to display it is like this:
Clientname
..............Project
What I want to do is like this:
Clientname
....Project
Is it possible to do this? How? Conditionally merge cells?
All help appreicated!
Kaisa M. LindahlYou are correct, merge the cells to get a colspan in a table. Simply
highlight the cells you want to merge (in the report designer) and
right click. You'll see then the option to Merge Cells.|||<cmarinella@.gmail.com> wrote in message
news:1104769372.216591.197530@.c13g2000cwb.googlegroups.com...
> You are correct, merge the cells to get a colspan in a table. Simply
> highlight the cells you want to merge (in the report designer) and
> right click. You'll see then the option to Merge Cells.
>
Ah, nice.
But I can only make it work in table view. Is it possible to do something
like it in matrix too?
Kaisa M. Lindahl