Pivot table

Question

User-1715141361 posted

Hello,

I have been going in circles with this all day.  I am hoping someone has the answer to what I am doing wrong.  Thanks for your time!

I have a simple answer set that I want to pivot:

Query result

WeekEndDate....EmpID.....GFName

2020-06-13 .........12345.....Joe Smith
2020-06-13..........12345......Linda Jones

I need the result to be:

WeekEndDate....EmpId......GF1..................GF2
2020-06-13.......12345.......Joe Smith.......Linda Jones

Here is what my query returns:

WeekEndDate....EmpId......GF1..................GF2
2020-06-13.......12345.........Null................ Null

My current query

SELECT * FROM
( SELECT WeekEndDate, PCH.[Id] EmpID, E.FirstName + ' ' + e.LastName GFName
FROM MM_Hours PCH, CrewDetail CD, Employee E
Where E.id = cd.GeneralForepersonId
and cd.Crewid = PCH.Crew and weekenddate = '2020-06-13' and PCH.id = 12345) s

PIVOT(Max(GFName)
FOR GFName
IN([GF1], [GF2])
) AS PivotGFS;

I am a beginner when it comes to pivot tables.

Thanks again

Answer 1

User77042963 posted

SELECT WeekEndDate,EmpId,[1] as GF1,[2] as GF2 
FROM
( SELECT 
ROW_NUMBER() Over(Partition by PCH.[Id],WeekEndDate Order by E.FirstName)  rn,
WeekEndDate, PCH.[Id] EmpID, E.FirstName + ' ' + e.LastName GFName
FROM MM_Hours PCH JOIN CrewDetail CD on cd.Crewid = PCH.Crew
JOIN Employee E on E.id = cd.GeneralForepersonId
WHERE weekenddate = '2020-06-13' and PCH.id = 12345
) s

PIVOT(Max(GFName) FOR rn IN([1], [2])
) AS PivotGFS;

Answer 2

User-1715141361 posted

Thank you limno for the help and the quick reply.

I just had to remove the stand alone Over() function to get it to work.

With it I was getting the error: "Function Row_Number must have an over clause with order by"

Once removed, the query ran successfully.