SQLTeam.com | Weblogs | Forums

Pivot Table, SQL Server 2008 R2

Hello guys, i have table as yellow area. How to query get result as blue area. Somebody help me please. Thank very much.
Untitled

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 :slight_smile: :slight_smile:

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)