MS SQL SERVER 如何把多列的值 , 变成一列多行 .

Question

SELECT '小名' AS c1 ,
'小名' AS c2 ,
'小名' AS c3 ,
'小名' AS c4 ,
'小名' AS c5 ,
'小名' AS c6 ,
'小名' AS c8

Answer 1

行转列，列转行是我们在开发过程中经常碰到的问题

行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。

但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性 .

--行转列的静态方案一：CASE WHEN,兼容sql2000
 select custid,
3 sum(case when YEAR(orderdate)=2002 then qty end) as [2002],
  sum(case when YEAR(orderdate)=2003 then qty end) as [2003],
  sum(case when YEAR(orderdate)=2004 then qty end) as [2004]
  from orders
  group by custid;

 --行转列的静态方案二：PIVOT,sql2005及以后版本
select *
 from (select custid,YEAR(orderdate) as years,qty from orders) as ord
pivot(sum(qty) for years in([2002],[2003],[2004]))as p

Answer 2

MS SQL SERVER没有这样的函数可以将多列转变为多行。
SELECT '小名' AS c1
union all
select '小名' AS c2
union all
select '小名' AS c3
union all
select '小名' AS c4
union all
select '小名' AS c5
union all
select '小名' AS c6
union all
select '小名' AS c8