元芳，对于各个sql需求你怎么看
--创建测试表
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
10杜甫2010-02-05 00:00:00.000100.00NULL贫困潦倒
21杜甫NULL200.00119.00大诗人
30元芳2012-02-06 00:00:00.0005000.00300.00大人，此事被后一定有一个天大的秘密
41元芳2012-02-07 00:00:00.0005100.00300.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)*/
[解决办法]
- SQL code
/*if exists (select 1 from sysobjects where id = object_id('celebrity') and type = 'U') drop table celebritycreate 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 allselect 1,'杜甫',null,200,119,'大诗人' union allselect 0,'元芳','2012-02-06',5000,300,'大人，此事被后一定有一个天大的秘密' union allselect 1,'元芳','2012-02-07',5100,300,'大人真乃神人也' */IF OBJECT_ID('FENGC','P') IS NOT NULL DROP PROC FENGCGOCREATE PROC FENGC (@SZ INT)AS/*参数为执行过程输入想要开始的列的位置FENGC 4*/CREATE TABLE LSB (ID INT IDENTITY(1,1) , TYPE VARCHAR(20), [NAME] VARCHAR(20), [FILE] VARCHAR(20), VALUE VARCHAR(40), ROWNUM INT )DECLARE @LM VARCHAR(40),@BJ INT,@MAX1 INT,@BJ2 INTSET @BJ = @SZSELECT @BJ2 = @SZ,@MAX1 = @BJ2+1 WHILE @BJ<=@MAX1BEGINIF OBJECT_ID('tempdb..#NAMES') IS NOT NULL DROP TABLE #NAMESSELECT name,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ROWNUM INTO #NAMESFROM syscolumns WHERE id = OBJECT_ID('celebrity','U')SELECT TOP 1 @LM = name,@MAX1 = MAX(ROWNUM) OVER() FROM #NAMES WHERE ROWNUM >=@BJ ORDER BY ROWNUMEXEC('DECLARE @LM1 VARCHAR(40),@BJ1 INTSET @BJ1 = '+@BJ+'IF OBJECT_ID(''tempdb..#NAMES1'') IS NOT NULL DROP TABLE #NAMES1SELECT name,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ROWNUM INTO #NAMES1FROM syscolumns WHERE id = OBJECT_ID(''celebrity'',''U'')SELECT TOP 1 @LM1 = name FROM #NAMES1 WHERE ROWNUM >=@BJ1WHILE @BJ1<='+@MAX1+'BEGIN INSERT INTO LSBSELECT type,name,''FILE'' = @LM1,CONVERT(VARCHAR(100),'+@LM+',121) AS VALUE,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM celebritySELECT @BJ1 = @BJ1+1END')SELECT @BJ=@BJ+1ENDSELECT [TYPE],[NAME],[FILE],[VALUE] FROM LSBGROUP BY [TYPE],[NAME],[FILE],[VALUE] ORDER BY [NAME],[FILE]DROP TABLE LSB/*TYPE NAME FILE VALUE-------------------- -------------------- -------------------- ----------------------------------------0 杜甫 Date 2010-02-05 00:00:00.0001 杜甫 Date NULL0 杜甫 Field_1 NULL1 杜甫 Field_1 119.000 杜甫 Field_2 贫困潦倒1 杜甫 Field_2 大诗人0 杜甫 Salary 100.001 杜甫 Salary 200.000 元芳 Date 2012-02-06 00:00:00.0001 元芳 Date 2012-02-07 00:00:00.0000 元芳 Field_1 300.001 元芳 Field_1 300.000 元芳 Field_2 大人，此事被后一定有一个天大的秘密1 元芳 Field_2 大人真乃神人也0 元芳 Salary 5000.001 元芳 Salary 5100.00(16 行受影响)*/