Consolidating Records

[jezmondo]

I have a table recording users and their PC's. The table is constructed so that if the user has more than one PC there are multiple entries for the user:

USER PCNAME
username1, PC01010
username1, PC01011
username1, PC01012
username2, PC01013
username2, PC01014

etc...

I need to extract these records so that there is one record per user:

USER PCNAME1 PCNAME2 PCNAME3
username1, PC01010, PC01011, PC01012
username2, PC01013, PC01014

Any ideas???

Thanks

Jez

 

[swampBoogie]

There is no standard function in SQL for this. It is usually easiest to handle in the application, i.e. get the data ordered by username, aggregate the values and write the previous data when a new username occurs.

 

[BillDHS]

You need to number the PC's the users have, ie:

USER PCNAME PCNum
username1, PC01010 , 1
username1, PC01011, 2
username1, PC01012, 3
username2, PC01013, 1
username2, PC01014, 2

Then:
Select User,
(Case When PCNum = 1 then PCname Else ' ' End),
(Case When PCNum = 2 then PCName Else ' ' End),
(case when PCNum =3 then PCName Else ' ' End
From TablePC

Add as many case statements as neccessary to cover the max number of PC's to one individual

 

[rwheels]

Assuming you're using MS SQL Server....there is a function in transact SQL called "pivot table" that works for this type of situation. It busts out the values of a field into columns, and aggregates the data. I'm not sure of the syntax, however. Read up on it in Books Online.