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.
| Author |
Topic |
|
csm
Starting Member
1 Post |
 Posted - 2003-05-31 : 12:31:43
|
I have a database (SQL Server 2000) with several tables, the critical tables are as follows:tblSubSystem: SystemID (foreign key to another table) SubSystemID (primary key) SubSystemName CurrentStatus LastStatus LastUpdate (some other critical attributes)tblSubSystemAttribs: SubSystemID (primary key) AttribName (primary key) AttribValue Basically the database represents the status of a system and all it's subsystems. Each subsystem has several attributes which are common with all subsystems (Name, Current Status, LastStatus, etc), and some attributes which some subsystems have, and others do not (eg. one subsystem has a control valve with a value to represent 'open' or 'closed', none of the others have).Up until recently this structure worked great. All queries relating to individual subsystems worked via table join and 'WHERE SubSystemID = x AND attribName = y' clauses.Now I need to represent the overall state of all subsystems in one table. In total there are 398 monitored attributes across all of the subsystems. As I said, some attributes are in common, others are not. What I need is to turn each SubSystemID and all it's attributes into a single row (there is 398 in total so I shouldn't hit the 1024 column maximum), inserting NULL into non-applicable attribute values, and using the attribName as the column name.HELP!!If anyone has any idea on how to do this I would REALLY appreciate it. I've been scratching my head now for 5 days and I haven't got a clue.Thanks |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-06-01 : 19:27:39
|
| Hi csm 1. could you post a couple of example data rows?2. why do you want all the info in 1 row - what are you going to do with it then?Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
|
|
|
Any ideas greatly appreciated...