如何实现MSSQL数据表行列转换

 

在做报表应用时,总是要涉及行行色色的数据操作，如题，就是一个很好的例子：

一、为了实现数据表行列转换，首先我们来创建一个数据表

CREATE TABLE MYTABLE

(

   myId int identity(1,1) constraint pk_myId primary  key,

   bookName varchar(50) not null,

   bookAuthor varchar(20) not null,

   bookPubDate datetime default getdate(),
  
   bookSaleA int ,

   bookSaleB int ,
  
   bookSaleC int

)

GO

INSERT INTO MYTABLE

   SELECT 'Spring入门','小孟',getDate(),54,52,48
      UNION ALL
   SELECT 'Spring进阶','小孟',getDate(),64,62,58
      UNION ALL
   SELECT 'proxool进阶','小刘',getDate(),33,32,28
      UNION ALL
   SELECT '加强oracle','孟进',getDate(),61,58,58
      UNION ALL
   SELECT '中国之象','刘自明',getDate(),133,132,128
      UNION ALL
   SELECT '加强MSSQL','孟进',getDate(),51,48,38

GO

SELECT * FROM MYTABLE

GO

显示的表数据为：

bookName    bookAuthor     bookPubDate        bookSaleA     bookSaleB    bookSaleC
Spring入门    小孟    2007-06-15 17:26:53.793   54           52             48 
Spring进阶    小孟    2007-06-15 17:26:53.793   64           62             58 
proxool进阶   小刘    2007-06-15 17:26:53.793   33           32             28 
加强oracle    孟进    2007-06-15 17:26:53.793   61           58             58 
中国之象     刘自明   2007-06-15 17:26:53.793   133          132            128 
加强MSSQL     孟进    2007-06-15 17:26:53.793   51           48             38 

二、自问：如果要把bookAuthor的列数据转换为行时，应该如何实现呢？

  
DECLARE @SQL VARCHAR(8000)  --为何要定义这么大呢？当你的列记录多的时候你就知道

SET @SQL = 'SELECT '  --SET 用于单行记录

SELECT @SQL = @SQL +  ' CASE bookAuthor when '''+bookAuthor+''' THEN 1 END ['+bookAuthor+'] ,' FROM (SELECT DISTINCT bookAuthor FROM MYTABLE)  AS TEMPTABLE --SELECT用于单行或者多行记录

SELECT @SQL = LEFT(@SQL,LEN(@SQL)-1) + 'INTO ##MYTABLETEMP FROM MYTABLE'

EXEC(@SQL)

SELECT * FROM ##MYTABLEtEMP

DROP TABLE ##MYTABLETEMP 

GO

查询结果：

刘自明  孟进  小刘  小孟

NULL   NULL   NULL   1
NULL   NULL   NULL   1
NULL   NULL   1      NULL
NULL   1      NULL   NULL
1      NULL   NULL   NULL
NULL   1      NULL   NULL

这样我们就实现了列到行的转换。

三、自问，既然能做到列到行的转换，那反之又如何呢？

我们将用列bookSaleA、bookSaleB、bookSaleC的数据转换为bookSaleAll列

DECLARE @SQL VARCHAR(8000)  --为何要定义这么大呢？当你的列记录多的时候你就知道

SET @SQL = '' --记得哦

SELECT @SQL = 'SELECT '''+name+''' AS bookSaleAll FROM MYTABLE UNION ALL '

             FROM syscolumns WHERE ColID LIKE 'bookSale%' ORDER BY ColID

SELECT @SQL = LEFT(@SQL,LEN(@SQL)-8)

EXEC(@SQL)

GO

注：

如果在列转为行时要把多行的数据压为一行，则需要使用max函数，如：

DECLARE @SQL VARCHAR(8000)  --为何要定义这么大呢？当你的列记录多的时候你就知道

SET @SQL = 'SELECT distinct  '  --SET 用于单行记录

SELECT @SQL = @SQL +  'max(CASE imgAppPageId when '''+imgAppPageId+''' THEN imgAppPageId else '''' END) ['+imgAppPageId+'] ,'
  FROM (SELECT DISTINCT imgAppPageId FROM PageImageTable )   AS TEMPTABLE --SELECT用于单行或者多行记录

print(@SQL)

SELECT @SQL = LEFT(@SQL,LEN(@SQL)-1) + 'INTO ##MYTABLETEMP FROM  PageImageTable '

print(@SQL)

EXEC(@SQL)

print(@SQL)

SELECT *  FROM ##MYTABLEtEMP 

DROP TABLE ##MYTABLETEMP