1. The original table test structure is as follows:
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/16/wKiom1SRCDyj_Is1AABlHsTZB-k835.jpg "title=" 1.png " alt= "Wkiom1srcdyj_is1aablhstzb-k835.jpg"/>
2. The result we want to get is:
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/16/wKiom1SRCIjyYmdnAABBtb-3S3Q100.jpg "title=" 2.png " alt= "Wkiom1srcijyymdnaabbtb-3s3q100.jpg"/>
3, the first method: Static method, that is, column fixed
SQL is as follows:
SELECT * FROM (select date,name,sum (num) num from Test GROUP by Date,name) tpivot (sum (num) for t.name in ([Alan],[andy] , [BAICL])) as a
4, the second method: Dynamic method, which is listed by SQL query get
SQL is as follows:
declare @sql nvarchar (4000) Select @sql =isnull (@sql + ', ', ') +t.namefrom (select DISTINCT QuoteName (name) as name from test ) Tset @sql = ' SELECT * FROM (select date,name,sum (num) num from Test GROUP by Date,name) T pivot (sum (num) to T.name in (' [email protected]+ ')] As a ' exec (@sql)
Database-like columns convert SQL