June 5, 2009 at 2:29 am
Hi All,
I have a hwakers database, and in this database I have tenant and product. A tenant can sell more than one product. now i would like to print a card with this tenant details as well as the products that he sells. now the problem is that if he selling more than one products, it gives more than one record and I would like for it to be just one record. Is this possible, if so how do I go about doing it? please see the attached PDF
June 5, 2009 at 2:39 am
Please give the table structure and the query that is used to get the detail of the tenant and product.
Tanx 😀
June 5, 2009 at 5:08 am
I have the structure of the table that is used. my problem is that a tenant sells more than one product and in the table you find that he has as many records as the products that he's selling.
June 5, 2009 at 5:16 am
You can build up a delimited string based on the product table, per tenant. There are many ways to go about this, depending on how much time you want to spend. Anything from CTE queries to WHILE loops to forward readonly cursors ...
That, or change the design of the card to handle a list of items on the back, for example.
June 8, 2009 at 7:08 am
Good Day,
the only thing i managed to do is using a cursor to get types of products, can you please give me an example of how to go about building a de3limited string.
thanx.
June 8, 2009 at 7:21 am
simply create a variable
declare @DelString varchar(max)
While @@fetch_status =0
@DelString = ',' + @DelString
Fetch next from cursor into Cursor variable
June 8, 2009 at 7:55 am
How are you printing this Card? What are you using to format it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 1:34 pm
Without sounding disrespectful, I think perhaps SQL is not the best way to do this. You will have more luck using Paste Special > Transpose in Microsoft Excel for each person's card. Then you can save that to CSV format and send that to the printer. If you're battling with delimited strings and so forth, this is my recommendation.
If I have offended you, I apologise in advance. This is not my intention.
June 9, 2009 at 12:59 am
i'M USING CRYSTAL REPORTS10
June 9, 2009 at 10:08 am
Nkagisang Bosaletse (6/9/2009)
i'M USING CRYSTAL REPORTS10
While I am not a Crystal Reports expert, I have used it before and IIRC, it should be able to handle the parent-child relationship here correctly and produce what you want more appropriately than SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2009 at 10:58 pm
Yes the best place to do this is crystal reports. You can use grouping in crystal reports based on tenants. And the repeated column can be placed in detail section.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply