分享之行列转换 SQL

Create a table :

1 CREATE TABLE MyTable(
2 　　A int NOT NULL,
3 　　B nvarchar(50) NOT NULL,
4 　　C nvarchar(50) NOT NULL
5 ) ON PRIMARY

 

Then insert some datas : 

 1　　 A           B               C
 2 　　--------- ------------- -------------
 3 　　1          date           10/10/08
 4 　　1          fname          jon
 5 　　1          lname          doe
 6 　　1          receipt        99999
 7 　　1          transnum       123
 8 　　55         date           10/10/04
 9 　　55         fname          allen
10 　　55         lname          smith
11 　　55         transnum       345
12 　　121        date           10/2/08
13 　　121        fname          sandra
14 　　121        lname          adams
15 　　121        receipt        99998
16 　　121        transnum       5465

Now we want to transform it to:

1 　　A      DATE      FNAME  LANME  RECEIPT  TRANSUM
2 　　1     10/10/08   jon    doe    99999    123
3 　　55    10/10/04   allen  smith  NULL     345
4 　　121   10/2/08    sandra adams  99998    5465

There are two simple solutions, and just use CASE and MAX : 

 

　　-- Static SQL :

 

1 　　SELECT A
2      　　, MAX(CASE B WHEN 'date'     THEN C ELSE ' ' END) AS DATE_COL
3      　　, MAX(CASE B WHEN 'fname'    THEN C ELSE ' ' END) AS FNAME
4      　　, MAX(CASE B WHEN 'lname'    THEN C ELSE ' ' END) AS LNAME
5      　　, MAX(CASE B WHEN 'receipt'  THEN C ELSE ' ' END) AS RECEIPT
6      　　, MAX(CASE B WHEN 'transnum' THEN C ELSE ' ' END) AS TRANSNUM
7 　　FROM MyTable
8 　　GROUP BY A

 

　　-- Dynamic SQL for B has more than these five rows (Date, fname, lname, receipt, transnum)

 

1 　　-- char(10) : New line
2 　　DECLARE @sql varchar(8000)
3 　　SET @sql = 'SELECT A ' + char(10)
4 　　SELECT @sql = @sql + ', MAX(CASE B WHEN '''+B + ''' THEN C ELSE '' '' END) AS '+ UPPER(B)+ char(10)
5 　　FROM (select distinct B from MyTable) as a
6 　　SET @sql = @sql + ' FROM MyTable GROUP BY A'
7 　　PRINT @sql
8 　　EXEC(@sql) 

转载于:https://www.cnblogs.com/yhuang/archive/2012/03/14/2397099.html