CSDN问答常用SQL问题汇总

一、行列转换

1、原表如图1，现在想实现图2效果，经典的行转列（问题地址：http://ask.csdn.net/questions/200875#answer_122366）

实现步骤：

具体的SQL代码如下：

select A as '运单明细',B as '运单编号', C as '付款方式', D as '运费' from T1;
 
Declare @sql varchar(8000)
Set @sql = 'Select B as 运单编号'
Select @sql = @sql + ',sum(case C when '''+C+''' then D else 0 end) [付款方式'+C+'的运费合计]'
from (select distinct C from T1) as T  --把所有唯一的付款方式的名称都列举出来
Select @sql = @sql+',sum(D) as ''全部运费合计'''
Select @sql = @sql+' from T1 group by B'
Exec (@sql)

Person

Orders

要求从上面这两张表中选出以下结果：

实现步骤：

具体SQL代码如下：

 select t.Id_p,t.LastName,COUNT(OrderNo) as ItemCount from 
(
select t1.OrderNo,ISNULL(t1.Id_p,t2.Id_p) as Id_p,isnull(t2.LastName,'Unknown') as LastName from Orders t1
full join  Person t2
on t1.Id_p = t2.Id_p
) t group by t.Id_p,t.LastName