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 |
|
Bertrandkis
Starting Member
8 Posts |
 Posted - 2006-03-20 : 03:47:17
|
| How can I create a cross-table from a flat table. The inintial table and cross table are shown below.Learner Course code Marks obtained--------------------------------------------------------Leticia Eksteen 9042 52Leticia Eksteen 9122 48Leticia Eksteen 9147 85Victoria Collins 9042 70Victoria Collins 9122 60Victoria Collins 9147 80John Best 9042 78John Best 9122 55John Best 9147 66John Best 9991 100The manager want to see the records in a cross-tab format like this:Learner code_9042 code_9122 code_9147 Code_9991L.Eksteen 52 48 85 NULLV.Collins 70 60 80 NULLJ.Best 78 55 66 100Le Bon vivant |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-20 : 05:20:17
|
| Or if you use v2005select Learner = left(Learner, 1) + '.' + right(Learner, charindex(' ', reverse(Learner))), code_9042 = [9042], code_9122 = [9122], code_9147 = [9147], code_9991 = [9991]from (select Learner, Course, Marksfrom #a) aPIVOT(sum(Marks) for Course in ([9042], [9122], [9147], [9991])) pvt(just wanted to see how easy it was)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Cross table