October 19, 2011 at 8:01 am
I currently receive data from three different types of clients. Each client has a different method in which they uniquely identify themselves.
I tried with the illustration below to show you how each of the clients do not all have the same type of information.
Name Product Participant Route
----------------------------------------------------------------------------------------------
Client A NA null 999999999
Client B NA 777777 NA
Client C 0000 NA NA
Being as each of these clients each have a different method of how they are identified, could one store all of this data into one table and say assign it each a unique key? Should I have a table for each type of client? Not sure of a proper approach for this.
October 19, 2011 at 8:06 am
CREATE TABLE dbo.ClientData
([Name]char(8),
[Product]char(4),
[Participant]char(6),
[Route]char(9))
INSERT INTO dbo.ClientData
VALUES ('Client A', 'NA', 'NA', '999999999')
INSERT INTO dbo.ClientData
VALUES ('Client B', 'NA', '7777777', 'NA')
INSERT INTO dbo.ClientData
VALUES ('Client C', '0000', 'NA', 'NA')
Added the table and data structure.
October 19, 2011 at 8:13 am
Would it work to have a ClientType column and a column to hold their key?
Something like:
CREATE TABLE dbo.ClientData
([Name] char(8),
[ClientType] char(10),
[IdentityValue] char(6),
That would eliminate two column of nulls on every record.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2011 at 9:05 am
Name Product Participant Route
----------------------------------------------------------------------------------------------
Client A NA null 999999999
Client B NA 777777 NA
Client C 0000 NA NA
could one store all of this data into one table and say assign it each a unique key?
I believe a simple CASE statement should work (if you have fixed integrations). You may use your regular table & populate it with multiple case statements.
Case A Then ... Column A Value A
Case B Then ... Column A Value B
Case C then ... Column A Value C
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply