一、行列转换
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