| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-06-24 : 11:43:52
|
Hi all,Sorry to be posting this question again, but I am still struggling with it and I would really appreciate some advice on the problem.Consider the following data :Title, Surname, DateOfBirth, RefNum, PercentageMr Coughlan 31/01/1955 00020 100.00Mr Murphy 18/07/1952 00021 33.33Mr Murphy 18/07/1952 00021 33.33Mr Murphy 18/07/1952 00021 33.33What I am trying to do is to restructure the data so that only one line exists for each person with the percentage field laid out horizontally. For example, Mr Murphy would have one line and his Percentage would be laid out as Percentage1, Percentage2 and so on. I am not sure how many Percentage field will be needed but 8 will be more than sufficient as I don’t think any records in my data exceed 6.Again, I apologise for posting this question here again, but I feel that this is a fundamental issue in SQL Server and perhaps an SQL Server Guru could help?The idea that I have at the moment is as follows :I select the distinct percentages from my source table. Say I have 10 possible percentages :5%, 10%, 15%, 20%, 30%, 50%, 60%, 80%, 90% and 100%If I create a table and assign a unique number to each row, so 5% would have an ID of 1, 10% will have an ID of 2 and so on.Then I insert another column in my source data and run an update statment where by it places the ID number next to each percentage value.Once that's done, I create a table with Percentage1, Percentage2 ... Percentage10and insert unique records of all my members. Then I can run an update on each Percentage column. Is this a good approach?Anyway, here is some sample data if you have a better way.CREATE TABLE #TEST(TITLE CHAR(5), SURNAME VARCHAR(30), DATEOFBIRTH CHAR(10), EMPLOYEEREFERENCE CHAR(10), PERCENTAGE NUMERIC(9,2))INSERT INTO #TEST(TITLE, SURNAME, DATEOFBIRTH, EMPLOYEEREFERENCE, PERCENTAGE)SELECT 'Ms', 'Doyle', '05/08/1937', '00019', NULLUNION ALLSELECT 'Mr', 'Coughlan', '31/01/1955', '00020', 100.00UNION ALLSELECT 'Mr', 'Murphy', '18/07/1952', '00021', 33.33UNION ALLSELECT 'Mr', 'Murphy', '18/07/1952', '00021', 33.33UNION ALLSELECT 'Mr', 'Murphy', '18/07/1952', '00021', 33.33UNION ALLSELECT 'Mr', 'Gallagher', '26/12/1952', '00024', 25.00UNION ALLSELECT 'Mr', 'Gallagher', '26/12/1952', '00024', 25.00UNION ALLSELECT 'Mr', 'Gallagher', '26/12/1952', '00024', 25.00UNION ALLSELECT 'Mr', 'Gallagher', '26/12/1952', '00024', 25.00UNION ALLSELECT 'Mr', 'Gallagher', '26/12/1952',' 00024', 25.00UNION ALLSELECT 'Mr',' Gallagher', '26/12/1952', '00024', 25.00 I look forward to your replies.P.SI searched for an article here but could not find anything of help. Has anyone done this kind of thing before?----------------Have a good day! |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-24 : 12:54:42
|
| Amethystium,your approach seems tedious but not half bad. |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-24 : 14:01:42
|
quote:
I feel that this is a fundamental issue in SQL Server ...
This is a fundamental design issue in relational databases generally, yes, but I will disagree with you here. It is almost never worth it in the long run to store data in a manner inconsistent with the relational scheme. If you want to present percentage data in a comma-delimited list, then do so in the presentation layer. It does not belong in the data layer.As a simple illustration of this, consider what options you might have if one set of users liked the comma-delimited list, and other set wanted a tab-delimited list. Would you create two tables? etc ...Jonathan{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-24 : 21:37:50
|
| This data:Title, Surname, DateOfBirth, RefNum, Percentage Mr Coughlan 31/01/1955 00020 100.00 Mr Murphy 18/07/1952 00021 33.33 Mr Murphy 18/07/1952 00021 33.33 Mr Murphy 18/07/1952 00021 33.33 belongs in at least 2 tables -- not one. you should have a table of "People":People-------TitleSurNameDateOfBirthRefNum (primary key ?)and a table of "Percentages" or whatever those percentages are for:Percentages-----------RefNumPct(other columns...)Hopefully "Percentages" will have some kind of primary key in there as well...Does that make sense? What is this data for? what does the Pct mean?Once that is done, I totally agree with "setbased..." -- you should always STORE your data in a format that is flexible and best suited for a relational database (i.e., normalized). As for how you display it, that is the job of whatever tool is displaying the data -- such as an ASP page or a report.Give us more info and we can help more ...- Jeff |
|
|
|
|
|
|
Transpose data - Help!