Two methods of SQL dynamic row-column _mssql

The first method:

Copy Code code as follows:

Select *from (select Url,case when month=01 then ' January ' when month=02 then ' February ' when month=03 then ' March ' when month=0 4 Then ' April ' when month=05 then ' May ' while month=06 then ' June ' when month=07 then ' July ' when month=08 then ' August ' when Mo nth=09 then ' September ' when month=10 then ' October ' when month=11 then ' November ' when month=12 then ' December '

End Month,quality from (

Select Url,datename (m,auditingtime) month,sum (Quality) Quality from Tb_order as a left join Tb_websiteinfo as B on A.webs Iteinfoid=b.id left join Tb_orderlist as C on c.orderid=a.id where auditingtime> ' 2013-01-01 ' and b.id>0 and Auditin g=2

Group by Url,datename (M,auditingtime) as h) as HH

Pivot (SUM (Quality) for month in ([January],[2 month],[3 month],[4 monthly],[5 month],[6 month],[7 months],[8 month],[9 month]) as a

The second method:

Copy Code code as follows:

DECLARE @sql varchar (8000)

Select @sql = isnull (@sql + ', ', ') + ' [' +convert (varchar (7), auditingtime,20) + '] '

From Tb_order as a left join Tb_websiteinfo as B in A.websiteinfoid=b.id left join Tb_orderlist as C on c.orderid=a.id where auditingtime> ' 2013-01-01 ' and b.id>0 and auditing=2

GROUP BY CONVERT (varchar (7), auditingtime,20) print @sql declare @sql2 varchar (8000) = ' Set @sql2 = ' Select *from (

Select Url, CONVERT (varchar (7), auditingtime,20) auditingtime,sum (Quality) Quality from Tb_order as a left join Tb_websit Einfo as B on a.websiteinfoid=b.id left join Tb_orderlist as C on C.orderid=a.id where b.id>0 and auditing=2

Group by URL, CONVERT (varchar (7), auditingtime,20)

As HH pivot (SUM (Quality) for auditingtime in (' + @sql + ')) B '

Print @sql2

EXEC (@sql2)