Ordering Pivot Table Using Case Else

I have created a Pivot table using Case Else with a combination of Row_Number function. What I'm looking for is to try to Order it in a specific way. Manivannan.D.Sekaran, helped me with another Pivot table that I had and it worked great. So I decided to learn how to do a Pivot table using Case Else. Sample Data is the following without the Case Else


UserID LastName FirstName DocumentDesc docFileName
1 Smith Paul Resume PSmithResume.pdf
1 Smith Paul PhdStatistics phdstatstranscript.pdf
1 Smith Paul MS Applied Statistics MsAStats.pdf
1 Smith Paul MS Operation Research MsOpResearch.pdf
2 Jackson Jane MS Information Systems MsInforSystems.pdf
2 Jackson Jane Resume JaneJacksonResume.pdf

This is my query for my Pivot using Case Else:




Code Snippet
Select UserID, LastName, FirstName,
MAX(Case When RecID=1 Then DocumentDesc Else '' End)As Document1,
Max(Case When RecID=1 Then docFileName Else '' End) As DocumentFileName,
Max(Case When RecID=2 Then DocumentDesc Else '' End)As Document2,
Max(Case When RecID=2 Then docFileName Else '' End) As DocumentFileName,
Max(Case When RecID=3 Then DocumentDesc Else '' End)As Document3,
MAX(Case When RecID=3 Then docFileName Else '' End) As DocumentFileName,
Max(Case When RecID=4 Then DocumentDesc Else '' End)As Document4,
Max(Case When RecID=4 Then docFileName Else '' End) As DocumentFileName,
Max(Case When RecID=5 Then DocumentDesc Else '' End)As Document5,
Max(Case When RecID=5 Then docFileName Else '' End) As DocumentFileName,
Max(Case When RecID=6 Then DocumentDesc Else '' End)As Document6,
Max(Case When RecID=6 Then docFileName Else '' End)As DocumentFileName
From (
Select a.UserID, a.LastName, a.FirstName, b.FileName, b.DocumentDesc, b.DocumentTypeID, ROW_NUMBER() OVER(PARTITION BY a.UserID ORDER BY a.UserID) AS RecID
FROM Person a JOIN
Documents b ON
a.UserID = b.UserID
Where b.DocumentTypeID = '126d2beb-f7a1-4bf1-b9c0-dded37d3a6bc' Or b.DocumentTypeID = '9087956e-1fb0-4f3d-ba33-ef31d79141af'
) X
Group by UserID, LastName, Firstname
Order by LastName







Code Snippet
The Output is the following with the Pivot applied: (I'm excluding UserID, LastName, FirstName for space purposes)

The column headings are:

Document1 DocumentFileName Document2 DocumentFileName Document3 DocumentFileName Document4 DocFilename

The Data for document and filename columns:

Ms Information Systems MsInforSystems.pdf Resume JaneJacksonResume.pdf

Resume PSmithResume.pdf PhdStatistics Phdstatstranscript.pdf Ms Applied Statistics MsAstats.pdf






What I want is to have the column called Document1 to have only Resumes and the rest of the other columns to have the other files.

The following data places the data into one table.

Create Table #data (
[UserID] int ,
[LastName] Varchar(100) ,
[FirstName] Varchar(100) ,
[DocumentDesc] Varchar(100) ,
[docFileName] Varchar(100)
);

Insert Into #data Values('1','Smith','Paul','Resume','PSmithResume.pdf');
Insert Into #data Values('1','Smith','Paul','PhdStatistics','phdstatstranscript.pdf');
Insert Into #data Values('1','Smith','Paul','MSAppliedStatistics','MsAstats.pdf');
Insert Into #data Values('1','Smith','Paul','MSOperationResearch', 'MsOpResearch.pdf');
Insert Into #data Values('2','Jackson','Jane','MsInformationSystems', 'MsInforSystems.pdf');
Insert Into #data Values('2','Jackson','Jane','Resume', 'JaneJacksonResume.pdf');