pivot - SQL Server : rows into columns -
i have 3 tables. need swith row values columns.
table-1: [approval_type]
app_id type_ds 2 rmc2 1 rmc1 table 2: [project]
pro_id summary 1 project1 2 project2 table:3 [prj_app]
app_id pro_id expdt comdt 1 2 2010-06-05 2010-07-06 1 1 1999-05-05 1999-05-06 2 1 1900-01-01 1900-01-05 i want display result as
pro_id rmc2 rmc2expedt rmc2comdt rmc1 rmc1expedt rmc1comdt 1 rmc2 1900-01-01 1900-01-05 rmc1 1999-05-05 1999-05-06 2 null null null rmc1 2010-06-05 2010-07-06 below query returns'
declare @sql1 nvarchar(max) = '' declare @sql nvarchar(max) = '' select @sql1 = stuff((select ',' + quotename(type_ds) + ',' + quotename(type_ds + ' expected date') + ',' + quotename(type_ds + ' completed date') dbo.apptype xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') print @sql1 set @sql = 'select * ( select a.pro_id, type_ds dbo.project left join [dbo].[prj_app] b on a.pro_id = b.pro_id right outer join dbo.apptype c on b.app_id = c.app_id ) data pivot ( max(type_ds) type_ds in (' + @sql1 + ') ) pvt1 pro_id not null' print @sql execute sp_executesql @sql pro_id rmc2 rmc2expedt rmc2comdt rmc1 rmc1expedt rmc1comdt 1 rmc2 null null rmc1 null null 2 null null null rmc1 null null. can on it...
my suggestion when working dynamic sql write query hard-coded first, can logic correct, convert dynamic sql.
since attempting pivot 3 columns of data first unpivot type_ds, expdt , comdt` columns, apply pivot function.
the hard-coded version of query be:
select * ( select pro_id, type_ds = case when col ='type_ds' type_ds else type_ds+col end, value ( select a.pro_id, c.type_ds, convert(varchar(10), b.expdt, 120) expdt, convert(varchar(10), b.comdt, 120) comdt dbo.project left join [dbo].[prj_app] b on a.pro_id = b.pro_id right outer join dbo.approval_type c on b.app_id = c.app_id ) s cross apply ( select 'type_ds', type_ds union select 'expdt', expdt union select 'comdt', comdt ) c (col, value) ) data pivot ( max(value) type_ds in (rmc2, rmc2expdt, rmc2comdt, rmc1, rmc1expdt, rmc1comdt) ) pvt1 see sql fiddle demo. have working version of query, can convert dynamic sql:
declare @sql1 nvarchar(max) = '' declare @sql nvarchar(max) = '' select @sql1 = stuff((select ',' + quotename(type_ds) + ',' + quotename(type_ds + 'expdt') + ',' + quotename(type_ds + 'comdt') dbo.approval_type xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @sql = 'select * ( select pro_id, type_ds = case when col =''type_ds'' type_ds else type_ds+col end, value ( select a.pro_id, c.type_ds, convert(varchar(10), b.expdt, 120) expdt, convert(varchar(10), b.comdt, 120) comdt dbo.project left join [dbo].[prj_app] b on a.pro_id = b.pro_id right outer join dbo.approval_type c on b.app_id = c.app_id ) s cross apply ( select ''type_ds'', type_ds union select ''expdt'', expdt union select ''comdt'', comdt ) c (col, value) ) data pivot ( max(value) type_ds in (' + @sql1 + ') ) pvt1 ' --print @sql execute sp_executesql @sql see sql fiddle demo
Comments
Post a Comment