Problem displaying data

Question

Problem displaying data

Sherif Maamoun

Ten Centuries

Points: 1254
More actions
July 6, 2005 at 12:04 am

#363098

I have two tables tableA is service table that is linked to Customers table tableB. For each service in TableA there may be one or more Customers. I want to create a view or procedure where I can display the data as follows:
ServiceID         Customer
1-A                 Customer1
                      Customer2
In other words I want to unite all customers with the same serviceID in one cell where each customer starts at a new line inside the same cell, meanwhile they all are in one row according to serviceID

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

You should do this on the client side as this is a presentation issue.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 2

The problem is I will be using this in generating a lot of other queries not just for presentation

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

This sounds like an unusual way to do things. Can you go into more detail about what you will be using the resultset for and perhaps we will find another, more straightforward, solution.

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 4

I am creating a main table under which there are a lot of services (around 10) and each service also has some other linked tables. At the end I need to create a summary of the main table listing all services and sub services with cost and sell to be used for accounting purposes and that is why I need to do this.

I have tried self joins but the problem is I get still four rows if I have two subservices and I am unable to eliminate those extra rows

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

I am sure that this can be achieved without the need for the query you originally requested.

Can you post sample details showing the query you are running, the output you get and the output you want?

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 6

SELECT dbo.tblMeetAssist.FolderID, dbo.tblMeetAssist.CategoryID, RTRIM(dbo.tblPaxList.Title) + SPACE(1) + RTRIM(dbo.tblPaxList.FirstName) + SPACE(1)

+ RTRIM(dbo.tblPaxList.LastName) + CHAR(13) + CHAR(10) + RTRIM(tblPaxList_1.Title) + SPACE(1) + RTRIM(tblPaxList_1.FirstName) + SPACE(1)

+ RTRIM(tblPaxList_1.LastName) AS Service, dbo.VendorCreditExpenseLine.VendorRefListID AS Vendor,

dbo.VendorCreditExpenseLine.ExpenseLineAmount AS Cost, dbo.tblMeetAssist.Sell, dbo.tblMeetAssist.Currency,

dbo.tblMeetAssist.TxnExchangeRate AS ExchangeRate, dbo.tblMeetAssist.ServiceRefNo

FROM dbo.tblMeetAssist INNER JOIN

dbo.tblPaxList ON dbo.tblMeetAssist.ServiceRefNo = dbo.tblPaxList.ServiceRefNo INNER JOIN

dbo.tblPaxList tblPaxList_1 ON dbo.tblPaxList.ServiceRefNo = tblPaxList_1.ServiceRefNo LEFT OUTER JOIN

dbo.VendorCreditExpenseLine ON dbo.tblMeetAssist.ServiceRefNo = dbo.VendorCreditExpenseLine.ServiceRefNO

This is the sample I am using and the results I get are:

**udvQuotationMeetAssist**
FolderID	CategoryID	Service	Vendor	Cost	Sell	Currency	ServiceRefNo
50000	2	Mr. Sherif Maamoun Mr. Sherif Maamoun	marriott kensington	100.00	200.00	L.E	MAS-2
50000	2	Mr. Sherif Maamoun Miss. Maie Helaly	marriott kensington	100.00	200.00	L.E	MAS-2
50000	2	Miss. Maie Helaly Mr. Sherif Maamoun	marriott kensington	100.00	200.00	L.E	MAS-2
50000	2	Miss. Maie Helaly Miss. Maie Helaly	marriott kensington	100.00	200.00	L.E	MAS-2

As you see I have only two names in this file but I get four rows instead of only one row (the correct one is row number 2)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

Sorry to mess you about, but this is tough to crack without also seeing the base data. As you've already identified, you're getting too many results as a consequence of having a many-to-many join on ServiceRefNo (my best guess). We should be able to get where you want through a combination of additional JOIN criteria and possibly a WHERE clause.

Can you just post a brief amount of base data - ideally what would produce the above results?

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 8

The main table is a service table that has a single servicerefno, we add to customers to the same service which carry the same servicerefno so it is one to many (one is the main service table) and the many is the customers table (it is a simple table with just an id number, servicerefno and customer data.

The main service table contains a lot of fields to describe the service details. This table is also linked to different vendor table through the servicerefno that provides the cost.

I hope this is helpful

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

But aren't your customers in tblPaxList? When you create the ServiceRefNo self join on tblPaxList, the returned recordset will be larger than required because ServiceRefNo is not unique on tblPaxList (so if ServiceRefNo = 2 exists in two records in tblPaxList, four records will be returned).

Your one-to-many join should be fine - I think it will be working how you want.

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sherif Maamoun Ten Centuries Points: 1254 More actions · Answer 10

Then how do you recommend a solution for this issue as sometimes even the customer number may be 3 , 4 or five....