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 |
|
ladiaocb
Yak Posting Veteran
57 Posts |
 Posted - 2007-01-29 : 14:50:35
|
| I have the following table:t1=id--------dataid-------datavalue1000------1------------supervisor11000------2------------2031000------3------------20060101The end result I am trying to come up with is:result =id------supervisor-------roomid-------hiredate1000----supervisor1------203----------20060101The id column represents a userid. Dataid 1 represents a supervisorname. Dataid 2 represents a room number. Dataid 3 represents a hiredate.I tried grouping the data but its not working the way I'd like. Any help is always appreciated! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-29 : 14:57:41
|
| select id, max(case when dataid = 1 then datavalue end) as supervisor,max(case when dataid = 2 then datavalue end) as roomid,max(case when dataid = 3 then datavalue end) as hiredatefrom t1group by idPeter LarssonHelsingborg, Sweden |
 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2007-01-29 : 15:04:10
|
| Please provide with another data set or 2 and the expected results as well.Its not clear whether the dataID is 1 for all supervisor names etc ...Also, ur database is not normalized.Srinika |
|
|
|
ladiaocb
Yak Posting Veteran
57 Posts |
Posted - 2007-01-29 : 15:06:41
|
| This works perfect! Thanks much! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-29 : 15:24:29
|
| Another EAV design.The horror! The horror!CODO ERGO SUM |
|
|
|
|
|
|
Grouping Data