How to convert rows in columns

Question

I have some problems with a SQL query, I try to convert row in columns.

select U.FirstName,U.LastName,Sh.[Day],Sh.StartTime,Sh.EndTime from dbo.[User] as U 
inner join dbo.Employee as E on U.UserId = E.UserId
inner join dbo.ScheduleStaff as SH on SH.EmployeeId = E.EmployeeId
where ((Sh.[Day] < getdate()+ 4  ) and sh.[Day] >= getdate()) 
group by U.FirstName, U.LastName,Sh.[Day],Sh.StartTime,Sh.EndTime

This query gives me the below result:

Query result

How can I make that there is one employee per line, and the days turn into columns?

I try this:

select FirstName,LastName,Day1,Day2,Day3
from
(
  select U.FirstName,U.LastName,Sh.[Day],Sh.StartTime,Sh.EndTime,concat('Day',ROW_NUMBER() over (partition by  
  U.FirstName,U.LastName order by U.FirstName,U.LastName)) as Tests
  from dbo.[User] as U 
  inner join dbo.Employee as E on U.UserId = E.UserId
  inner join dbo.ScheduleStaff as SH on SH.EmployeeId = E.EmployeeId
  where ((Sh.[Day] <  getdate()+ 4  ) and sh.[Day] >= getdate()-1) 
  group by U.FirstName, U.LastName,Sh.[Day],Sh.StartTime,Sh.EndTime
)Temp 
pivot 
(
  Max([Day]) 
  for Tests in (Day1,Day2,Day3)
)Piv

But this is not exactly what I need, how can I make the days as columns and their time as values?

Output result

Does this answer your question? [Group by column and multiple Rows into One Row multiple columns](https://stackoverflow.com/q/63521138/2029983)? If not, why not? — Thom A, Aug 28 '20 at 08:22
It sounds like you need to perform a **dynamic** pivot. Maybe this thread will help:[SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Terence, Aug 28 '20 at 08:48
You pivot day values (`Max([Day])`) into constant columns (`in (Day1, Day2, Day3)`). You want to pivot _hour_ values (`max(StartTime)`) into dynamic columns (`in ([2020-08-28], [2020-08-31], [2020-09-01])`). Look at Terence's link to get started. — Sander, Aug 28 '20 at 09:05

Ahmmed · Answer 1 · 2020-08-28T17:11:29.933

This will work if you have static [Date] value. But if you want [Date] dynamic then you need to collect date value and put into pivot section.

Your question was[how can I make the days as columns and their time as values]

select FirstName,LastName,[2020-08-28],[2020-08-29] from 
(select FirstName,LastName,[Date], CONCAT(StartTime,'-to-'+EndTime) as [Time] from tbl_user) t
pivot(MAX(t.[Time]) for [Date] in([2020-08-28],[2020-08-29]) )pvt

Note: for dynamic date value. You can follow Terence link mentioned in comment section.

How to convert rows in columns

1 Answers1