2

I've been approaching a problem perhaps in the wrong way. I've researched pivot examples

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query How to create a pivot query in sql server without aggregate function

but they aren't the type I'm looking for.. or perhaps I'm approaching this in the wrong way, and I'm new to sql server.

I want to transform:

Student:

studid | firstname | lastname | school 
-----------------------------------------
1          mike         lee       harvard 
1          mike         lee       ucdavis
1          mike         lee       sfsu 
2          peter        pan       chico
2          peter        pan       ulloa
3          peter        smith     ucb

Desired output: (note for school, want only 3 columns max.)

studid| firstname | lastname   | school1  | school2 | school3
---------------------------------------------------------------------
1       mike        lee           Harvard   ucdavis   sfsu  
2       peter       pan           chico     ulloa
3       peter       smith         ucb

The tutorials I see shows the use of Sum() , count() ... but I have no idea how to pivot string values of one column and put them into three columns.

Zach Smith
  • 8,458
  • 13
  • 59
  • 133
Derick
  • 135
  • 1
  • 3
  • 12

1 Answers1

2

You can get the results you desire by taking max(school) for each pivot value. I'm guessing the pivot value you want is rank over school partitioned by student. This would be the query for that:

select * from
(select *, rank() over (partition by studid order by school) rank from student) r
pivot (max(school) for rank in ([1],[2],[3])) pv

note that max doesn't actually do anything. the query would return the same results if you replaced it with min. just the pivot syntax requires the use of an aggregate function here.

gordy
  • 9,360
  • 1
  • 31
  • 43
  • I'm totally new to sql server. I'm trying to figure out what rank() over (partition ...) does. – Derick Jan 16 '15 at 02:22
  • you can run just the inner query to see what it does. or read about it here: [rank t-sql msdn](http://msdn.microsoft.com/en-us/library/ms176102.aspx) – gordy Jan 16 '15 at 05:31