Hello guys, i have table as yellow area. How to query get result as blue area. Somebody help me please. Thank very much.
If you post directly usable data -- CREATE TABLE and INSERT statement(s) -- you'll get many more people who can respond.
hi
hope this helps 
this looks like the solution !!!
i used dynamic pivot ....
drop create data ...
Drop table #data
Go
Create table #data
(
Date1 date ,
Name varchar(100) ,
Work varchar (100)
)
go
Insert into #data select
'01-01-2019','john','security'
Insert into #data select
'01-01-2019','marry','driver'
Insert into #data select
'01-01-2019','nick','receptionist'
Insert into #data select
'01-01-2019','tom','security'
Select *from #data
Go
SQL ....
DECLARE @cols AS NVARCHAR(max),
@query AS NVARCHAR(max);
SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(c.work)
FROM #data c
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)
PRINT @cols
SET @query = 'SELECT date1, ' + @cols
+ ' from ( select * from (SELECT OutTab.date1 ,OutTab.[work] , name = STUFF ( ( SELECT '',''+InrTab.name FROM #data InrTab WHERE InrTab.date1 = OutTab.date1 AND InrTab.work = OutTab.work ORDER BY InrTab.name FOR XML PATH(''''),TYPE ).value(''.'',''VARCHAR(MAX)'') , 1,1,SPACE(0)) FROM #data OutTab GROUP BY OutTab.date1 , OutTab.work ) a ) x pivot ( max(name) for work in (' + @cols
+ ') ) p '
EXECUTE(@query)
