分享之行列转换 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)
posted @ 2012-03-14 23:42  yan.h  阅读(455)  评论(2编辑  收藏  举报