I have a table in sql server like this

TblSchedule( Date, EmpName, ShiftName)

i want to display table in asp.net page like this

Date	Morning shift	Afternoon shift	Night shift
07/19/2015	Tony	Jack	Tony, Mohit
07/20/2015	Rocky	Jocky, Man	Romy

How can i display this. help me

• Moved by Bob BeaucheminMVP 10 hours 42 minutes ago Moved to a more relevant forum for best results

try this:

if object_id('dbo.TblSchedule') is not null 
    drop table dbo.TblSchedule
Create table dbo.TblSchedule
(
 [Date] DATE,
 EmpName VARCHAR(50),
 ShiftName VARCHAR(50)
)

insert into dbo.TblSchedule values
('07/19/2015','Tony','Morning'),
('07/19/2015','Jack','Afternoon'),
('07/19/2015','Tony','Night'),
('07/19/2015','Mohit','Night'),
('07/20/2015','Rocky','Morning'),
('07/20/2015','Jocky','Afternoon'),
('07/20/2015','Man','Afternoon'),
('07/20/2015','Romy','Night')



;with CTE AS 
(
SELECT [DATE],ShiftName, EmpName = 
    STUFF((SELECT ', ' + EmpName
           FROM dbo.TblSchedule b 
           WHERE b.[Date] = a.[Date] and b.ShiftName = a.ShiftName
          FOR XML PATH('')), 1, 2, '')
FROM dbo.TblSchedule a
GROUP BY [Date],ShiftName
)

select ts.[Date],max(cmorning.EmpName) as MorningShift,max(cafternoon.EmpName) as AfternoonShift,max(cNight.EmpName) as NightShift
from dbo.TblSchedule ts
LEFT JOIN CTE cmorning on ts.[Date] = cmorning.[Date] and cmorning.ShiftName = 'Morning'
LEFT JOIN CTE cafternoon on ts.[Date] = cafternoon.[Date] and cafternoon.ShiftName = 'Afternoon'
LEFT JOIN CTE cNight on ts.[Date] = cNight.[Date] and cNight.ShiftName = 'Night'
group by ts.[Date]
ORDER BY Ts.[Date]

Moving to a more relevant (T-SQL) forum because the code given was in T-SQL, although there is a general opinion that reformatting should be accomplished through client-side code. If your question is about ASP.NET displaying, please repost to the ASP.NET forums here: http://forums.asp.net/

Cheers, Bob

Using PIVOT relational operator.

-- code #1
with TblSchedule_2 as (
SELECT [Date], ShiftName, 
     EmpName= 
      STUFF((SELECT ', ' + EmpName
               from dbo.TblSchedule as B 
               where B.[Date] = A.[Date] and B.ShiftName = A.ShiftName
               For XML Path('')), 1, 2, '')
  from dbo.TblSchedule as A
  group by [Date], ShiftName
)
SELECT [Date], 
       [Morning] as [Morning shift],
       [Afternoon] as [Afternoon shift], 
       [Night] as [Night shift]
  from TblSchedule_2
       pivot (Max(EmpName) for ShiftName in ([Morning], [Afternoon], [Night])) as P;

CTE code by

Thankyou VeryMuch

How to Add  Date BETWEEN FromDate AND UptoDate