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 |
|
Mr Butter
Starting Member
3 Posts |
 Posted - 2011-10-19 : 22:06:07
|
| I have a table called s1 that has participants with social security numbers (SSN),name (NM), and a case number (CN). The case number can have more than one participant.ssn NM CN 123 jim 100124 Steve 2oo125 Sam 100I want the results to equal the following:cn nm nm1 100 jim sam200 Steve (null)Please excuse the simplicity of this request. Thank you M.Butter |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-20 : 00:36:55
|
| [code]SELECT CN,MAX(CASE WHEN rn=1 THEN NM ELSE NULL END) AS NM1,MAX(CASE WHEN rn=2 THEN NM ELSE NULL END) AS NM2FROM (select row_number() over (partition by CN order by ssn) as rn,* from table)tGROUP BY CN[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Rows to columns