Re-structuring data in table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Re-structuring data in table

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-24 : 05:47:22

Hi all,

I have the following data :


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', NULL
UNION ALL
SELECT	'Mr', 'Coughlan', '31/01/1955', '00020', 100.00
UNION ALL
SELECT	'Mr', 'Murphy', '18/07/1952', '00021', 33.33
UNION ALL
SELECT	'Mr', 'Murphy', '18/07/1952', '00021', 33.33
UNION ALL
SELECT	'Mr', 'Murphy', '18/07/1952', '00021', 33.33
UNION ALL
SELECT	'Mr', 'Gallagher', '26/12/1952', '00024', 25.00
UNION ALL
SELECT	'Mr', 'Gallagher', '26/12/1952', '00024', 25.00
UNION ALL
SELECT	'Mr', 'Gallagher', '26/12/1952', '00024', 25.00
UNION ALL
SELECT	'Mr', 'Gallagher', '26/12/1952', '00024', 25.00
UNION ALL
SELECT	'Mr', 'Gallagher', '26/12/1952',' 00024', 25.00
UNION ALL
SELECT	'Mr',' Gallagher', '26/12/1952', '00024', 25.00

The table contains multiple records for employeereference 00024. What I would like to do is populate another table so that the percentages are horizontal. For example, Employeereference 00024 would have one line and extra columns for the percentages (Perc1, Perc2, Perc3 etc..)

Any suggestions?!

----------------
Have a good day!

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-24 : 06:15:46

This is the closest with the data provided.

select
distinct ltrim (a.EMPLOYEEREFERENCE), TITLE, ltrim (SURNAME), DATEOFBIRTH, b.percentage
FROM
#test a
LEFT OUTER JOIN
(
SELECT EMPLOYEEREFERENCE, max(percentage) as percentage
FROM #test
group by EMPLOYEEREFERENCE
) b on a.EMPLOYEEREFERENCE = b.EMPLOYEEREFERENCE

To produce the other effect that you want, splitting it so that you had
percentage1 percentage2 etc

I think there would need to be some method by which you could uniquely identify each percentage. With the data provided, I cannot do this.

The only thing I can think of is that you could add an identity column to your data set then you can split the percentages off, but the realisation of this is beyond me.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources