第一種方法：

代碼如下:

select *from ( select Url,case? when? Month=01 then? ‘1月’ when? Month=02 then ‘2月’ when? Month=03 then? ‘3月’ when? Month=04 then ‘4月’ when? Month=05 then? ‘5月’ when? Month=06 then ‘6月’ when? Month=07 then? ‘7月’ when? Month=08 then ‘8月’ when? Month=09 then? ‘9月’ when? Month=10 then ‘ 10月’ when? Month=11 then? ’11月’ when? Month=12 then ‘ 12月’

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.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 Url,DATENAME(M,AuditingTime) )as h ) as hh

pivot ( sum(Quality) for month in([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) as a

第二種方法：

代碼如下:

declare @sql varchar(8000)

select @sql = isnull(@sql + ‘,’ , ”) + ‘[‘+CONVERT(varchar(7),AuditingTime,20)+’]’

from? tb_order as a left join? tb_WebSiteInfo as b on 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_WebSiteInfo 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)

您可能感興趣的文章:mysql 行轉列和列轉行實例詳解sql語句實現行轉列的3種方法實例SQLServer行轉列實現思路記錄MySQL存儲過程中使用動態行轉列mssql 數據庫表行轉列，列轉行終極方案Sql Server 2000 行轉列的實現(橫排)SQL查詢語句行轉列橫向顯示實例解析table 行轉列的sql詳解SQL行轉列和列轉行代碼詳解SQL基礎教程之行轉列Pivot函數

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的mysql行转列sql函数_sql动态行转列的两种方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。