January 29, 2007 at 5:28 pm
Hi, I need a little help in figuring out how to correctly select data to fit into a grid. Specifically I have 3 tables:
1) Software (ID int, Name varchar, Description varchar)
2) Client (ID int, Name varchar, Address varchar)
3) Customizations (CustomizationID int, SoftwareID int, ClientID int, ClientSoftwareCustomization varchar)
These are the constraints on the data: For a single Client and Software combination there is only one Customization (basically a long string if there are multiple customizations but concatenated so there is only one entry in the table)
I would like a result set to look like this:
| SW Name 1 | SW Name 2 | SW Name 3 | SW Name 4 | SW Name(...)
Client 1| Custom 1-1 |Custom 1-2 | Custom 1-3 | Custom 1-4 |Custom 1-... |
Client 2| Custom 2-1 | null | Custom 2-3 | Custom 2-4 |Custom 2-... |
Client 3| Custom 3-1 |Custom 3-2 | null | Custom 3-4 |Custom 3-... |
Client...|Custom...-1 |Custom ...-2| null |Custom...-4 |Custom...-... |
I have placed null in the above example where the relationship does not exist, ie. the client has no customization need for that particular piece of software.
Anyone have ideas how to generate results like this? I appreciate any help.
Dan
January 31, 2007 at 12:27 pm
Hello
I would try to change the constraint. The Customization table looks like a nromal linked table (3 rd Normal Form?) . WHy would you like to concatenate strings and separate them only with commas? It would be hard to parse such entry in the future and this is why it is not trivcial to answer your question.
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply