Diango


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');





Re: Ordering Pivot Table Using Case Else

Manivannan.D.Sekaran


Here it is, Try to use the CTE (common table expression) rather than th derived tables,

Code Snippet

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');

Code Snippet

;With CTE

as

(

Select

UserId,

Lastname,

FirstName,

DocumentDesc,

DocFileName,

Row_Number() Over(Partition By UserId Order By Case When DocumentDesc = 'Resume' Then 0 Else 1 End, UserId) Rid

From

#data

)

Select

UserId,

Lastname,

FirstName,

max(Case When Rid=1 Then DocumentDesc Else '' End) as [DocumentDesc1],

max(Case When Rid=1 Then DocFileName Else '' End) as [DocFileName1],

max(Case When Rid=2 Then DocumentDesc Else '' End) as [DocumentDesc2],

max(Case When Rid=2 Then DocFileName Else '' End) as [DocFileName2],

max(Case When Rid=3 Then DocumentDesc Else '' End) as [DocumentDesc3],

max(Case When Rid=3 Then DocFileName Else '' End) as [DocFileName3],

max(Case When Rid=4 Then DocumentDesc Else '' End) as [DocumentDesc4],

max(Case When Rid=4 Then DocFileName Else '' End) as [DocFileName4],

max(Case When Rid=5 Then DocumentDesc Else '' End) as [DocumentDesc5],

max(Case When Rid=5 Then DocFileName Else '' End) as [DocFileName5]

From

CTE

Group By

UserId, LastName, FirstName







Re: Ordering Pivot Table Using Case Else

Diango

Why is it better to use CTE rather than derived tables







Re: Ordering Pivot Table Using Case Else

hunchback

Hi Manivannan,

I think that we should use:

row_number() over(partition by UserId order by case when DocumentDesc = 'Resume' then 0 else 1 end, DocumentDesc) as Rid

AMB