--创建测试表
if exists (select 1 from sysobjects where id = object_id('celebrity') and type = 'U')
drop table celebrity
create table celebrity(
[id] int identity(1,1) primary key,
[type] int ,
[name] nvarchar(50),
[Date] datetime,
[Salary] decimal(18,2),
[Field_1] decimal(18,2),
[Field_2] nvarchar(100)
)
--插入初始测试数据
insert into celebrity([type],[name],[Date],[Salary],[Field_1],[Field_2])
select 0,'杜甫','2010-02-05',100,null,'贫困潦倒' union all
select 1,'杜甫',null,200,119,'大诗人' union all
select 0,'元芳','2012-02-06',5000,300,'大人，此事被后一定有一个天大的秘密' union all
select 1,'元芳','2012-02-07',5100,300,'大人真乃神人也'
select * from celebrity order by name
/*
初始测试数据
id type name createDate Salary Field_1 Field_2
1 0 杜甫 2010-02-05 00:00:00.000 100.00 NULL 贫困潦倒
2 1 杜甫 NULL 200.00 119.00 大诗人
3 0 元芳 2012-02-06 00:00:00.000 5000.00 300.00 大人，此事被后一定有一个天大的秘密
4 1 元芳 2012-02-07 00:00:00.000 5100.00 300.00 大人真乃神人也
希望得到结果
id type name File value
1 0 杜甫 createDate 2010-02-05 00:00:00.000
2 1 杜甫 createDate NULL
3 0 杜甫 Salary 100.00
4 1 杜甫 Salary 200.00
5 0 杜甫 Field_1 NULL
6 1 杜甫 Field_1 119.00
7 0 杜甫 Field_2 贫困潦倒
8 1 杜甫 Field_2 大诗人
9 0 元芳 createDate 2012-02-06 00:00:00.000
10 1 元芳 createDate 2012-02-07 00:00:00.000
11 0 元芳 Salary 5000.00
12 1 元芳 Salary 5100.00
13 0 元芳 Field_2 大人，此事被后一定有一个天大的秘密
14 1 元芳 Field_2 大人真乃神人也
*/
------解决方案--------------------
元芳你怎么看。？
------解决方案--------------------
- SQL code
create table celebrity([id] int identity(1,1) primary key, [type] int , [name] nvarchar(10), [createDate] datetime, [Salary] decimal(18,2), [Field_1] decimal(18,2), [Field_2] nvarchar(100))insert into celebrity([type],[name],[createDate],[Salary],[Field_1],[Field_2])select 0,'杜甫','2010-02-05',100,null,'贫困潦倒' union allselect 1,'杜甫',null,200,119,'大诗人' union allselect 0,'元芳','2012-02-06',5000,300,'大人，此事被后一定有一个天大的秘密' union allselect 1,'元芳','2012-02-07',5100,300,'大人真乃神人也' select row_number() over(order by name,case [File] when 'createDate' then 1 when 'Salary' then 2 when 'Field_1' then 3 when 'Field_2' then 4 end,id) 'id',type,name,[File],valuefrom(select id,type,name,col 'File',v 'value' from (select id,type,name, isnull(convert(nvarchar(50),createDate,23),'Null') 'createDate', isnull(cast(Salary as nvarchar(50)),'Null') 'Salary', isnull(cast(Field_1 as nvarchar(50)),'Null') 'Field_1', isnull(cast(Field_2 as nvarchar(50)),'Null') 'Field_2' from celebrity) c unpivot(v for col in(createDate,Salary,Field_1,Field_2)) u) t/*id type name File value-------------------- ----------- ---------- ---------------- -----------------------------------1 0 杜甫 createDate 2010-02-052 1 杜甫 createDate Null3 0 杜甫 Salary 100.004 1 杜甫 Salary 200.005 0 杜甫 Field_1 Null6 1 杜甫 Field_1 119.007 0 杜甫 Field_2 贫困潦倒8 1 杜甫 Field_2 大诗人9 0 元芳 createDate 2012-02-0610 1 元芳 createDate 2012-02-0711 0 元芳 Salary 5000.0012 1 元芳 Salary 5100.0013 0 元芳 Field_1 300.0014 1 元芳 Field_1 300.0015 0 元芳 Field_2 大人，此事被后一定有一个天大的秘密16 1 元芳 Field_2 大人真乃神人也(16 row(s) affected)*/