两次行转列的sql写法

2013-12-19

求一个两次行转列的sql写法[解决办法]距我了解貌似纯SQL搞不了两层，一层是没问题的[解决办法]引用:先谢谢

求一个两次行转列的sql写法

[解决办法]
距我了解貌似纯SQL搞不了两层，一层是没问题的
[解决办法]

引用:

先谢谢了，一小时后我就可以电脑上网了

这样吗：

CREATE TABLE [dbo].[t1](
[Code] [nvarchar](30) NULL,
[Stype] [nvarchar](20) NULL,
[Dtype] [nvarchar](20) NOT NULL,
[DValue] [nvarchar](40) NULL
)

INSERT INTO T1
SELECT '12303','100','10','4-19' UNION ALL
SELECT '12303','100','20','4-19' UNION ALL
SELECT '12303','100','40','4-26' UNION ALL
SELECT '12303','200','10','4-19' UNION ALL
SELECT '12303','200','20','4-19' UNION ALL
SELECT '12303','200','40','4-26' UNION ALL
SELECT '12303','200','10','5-2' UNION ALL
SELECT '12303','200','30','5-11' UNION ALL
SELECT '12303','200','50','5-13' UNION ALL
SELECT '12303','300','10','5-6' UNION ALL
SELECT '12303','300','20','5-12' UNION ALL
SELECT '12303','300','50','5-14' UNION ALL
SELECT '12303','400','10','5-8' UNION ALL
SELECT '12303','400','30','5-15' UNION ALL
SELECT '12303','400','50','5-17' UNION ALL
SELECT '12303','500','10','5-15'
go

declare @sql nvarchar(4000)

set @sql = ''

select @sql = @sql + ',max(case when stype = '''+stype+
              ''' and dtype='''+Dtype+''' then Dvalue else null end) '+
              stype_v+'_'+Dtype_v
from
(
select Stype,
       case Stype when  '100' then '初样'
  when '200' then '尺寸样'
  when '300' then '产前样'
  when '400' then '销售样'
  when '500' then '船样'
   end as stype_v,

       Dtype,
       case Dtype when '10' then '资料接收时间'
  when '20' then '交付版房时间'
  when '30' then '领料时间'
  when '40' then '预计交样时间'
  when '50' then '实际完成时间'
       end as Dtype_v
from
(
select distinct Stype
from t1
)t1
inner join
(
select distinct Dtype
from t1
)t2
on 1=1
)t

select @sql = 'select code as 款号'+@sql +
              ' from t1 group by code'

exec(@sql)
/*
款号初样_资料接收时间初样_交付版房时间初样_领料时间初样_预计交样时间初样_实际完成时间尺寸样_资料接收时间尺寸样_交付版房时间尺寸样_领料时间尺寸样_预计交样时间尺寸样_实际完成时间产前样_资料接收时间产前样_交付版房时间产前样_领料时间产前样_预计交样时间产前样_实际完成时间销售样_资料接收时间销售样_交付版房时间销售样_领料时间销售样_预计交样时间销售样_实际完成时间船样_资料接收时间船样_交付版房时间船样_领料时间船样_预计交样时间船样_实际完成时间
123034-194-19NULL4-26NULL5-24-195-114-265-135-65-12NULLNULL5-145-8NULL5-15NULL5-175-15NULLNULLNULLNULL
*/

热点排行

SQL Server

两次行转列的sql写法