行转列的问题

CREATE TABLE [ZJSPL] (
    [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [DEP] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [LEV] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [OKData] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    CONSTRAINT [PK_ZJSPL] PRIMARY KEY  CLUSTERED 
    (
        [CODE],
        [DEP],
        [LEV]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'CG', N'00', N'采购完成日期', N'2008-1-1')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'CG', N'01', N'面料', N'OK')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'CG', N'02', N'里料', N'')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'JS', N'01', N'设计稿接收日期', N'2008-12-11')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'JS', N'02', N'打版完成日期', N'2008-12-13')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'JS', N'03', N'初样完成日期', N'2008-12-14')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'JS', N'04', N'再样完成日期', N'2008-12-15')
INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
VALUES (N'123456', N'JS', N'05', N'齐色完成日期', N'2008-12-16')
select isnull(a.CODE,b.code) as code, 
       isnull(a.DEP,'') as dep, 
   isnull(a.LEV,'') as lev,
       isnull(a.[Name],'') as [name], 
       isnull(a.OKData,'') as OKdate,
       b.DEP, b.LEV, b.[Name],b.OKData  
from (select rid =row_number() over (partition by code order by lev),
      * 
      from [ZJSPL]
  where dep = 'cg') a
full join (select rid =row_number() over (partition by code order by lev),
      * 
      from [ZJSPL]
      where dep = 'js') b 
on a.code = b.code and a.rid = b.rid /*
code                 dep                  lev                  name                                               OKdate                                             DEP                  LEV                  Name                                               OKData
-------------------- -------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------- -------------------- -------------------------------------------------- --------------------------------------------------
123456               CG                   00                   采购完成日期                                             2008-1-1                                           JS                   01                   设计稿接收日期                                            2008-12-11
123456               CG                   01                   面料                                                 OK                                                 JS                   02                   打版完成日期                                             2008-12-13
123456               CG                   02                   里料                                                                                                    JS                   03                   初样完成日期                                             2008-12-14
123456                                                                                                                                                               JS                   04                   再样完成日期                                             2008-12-15
123456                                                                                                                                                               JS                   05                   齐色完成日期                                             2008-12-16(5 行受影响)
8/
drop table [ZJSPL]

解决方案 »

  1.   

    服务器: 消息 195，级别 15，状态 10，行 4
    'row_number' 不是可以识别的 函数名。
      

  2.   


    2000CREATE TABLE [ZJSPL] (
        [CODE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [DEP] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [LEV] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
        [OKData] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
        CONSTRAINT [PK_ZJSPL] PRIMARY KEY  CLUSTERED 
        (
            [CODE],
            [DEP],
            [LEV]
        )  ON [PRIMARY] 
    ) ON [PRIMARY]
    GO
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'CG', N'00', N'采购完成日期', N'2008-1-1')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'CG', N'01', N'面料', N'OK')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'CG', N'02', N'里料', N'')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'JS', N'01', N'设计稿接收日期', N'2008-12-11')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'JS', N'02', N'打版完成日期', N'2008-12-13')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'JS', N'03', N'初样完成日期', N'2008-12-14')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'JS', N'04', N'再样完成日期', N'2008-12-15')
    INSERT INTO ZJSPL(CODE, DEP, LEV, Name, OKData)
    VALUES (N'123456', N'JS', N'05', N'齐色完成日期', N'2008-12-16')select isnull(a.CODE,'') as code, 
           isnull(a.DEP,'') as dep, 
           isnull(a.LEV,'') as lev,
           isnull(a.[Name],'') as [name], 
           isnull(a.OKData,'') as OKdate,
           b.DEP, b.LEV, b.[Name],b.OKData  
    from (select rid =(select count(1) from [ZJSPL] where lev <c.lev and dep = 'cg'),
          * 
          from [ZJSPL] c
          where dep = 'cg') a
    full join 
         (select rid =(select count(1) from [ZJSPL] where lev <d.lev and dep = 'js'),
          * 
          from [ZJSPL] d
          where dep = 'js') b 
    on a.code = b.code and a.rid = b.rid
     /*
    code                 dep                  lev                  name                                               OKdate                                             DEP                  LEV                  Name                                               OKData
    -------------------- -------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------- -------------------- -------------------------------------------------- --------------------------------------------------
    123456               CG                   00                   采购完成日期                                             2008-1-1                                           JS                   01                   设计稿接收日期                                            2008-12-11
    123456               CG                   01                   面料                                                 OK                                                 JS                   02                   打版完成日期                                             2008-12-13
    123456               CG                   02                   里料                                                                                                    JS                   03                   初样完成日期                                             2008-12-14
                                                                                                                                                                         JS                   04                   再样完成日期                                             2008-12-15
                                                                                                                                                                         JS                   05                   齐色完成日期                                             2008-12-16
       
    (5 行受影响)
    */
    drop table [ZJSPL]
      

  3.   

    是SQL2000谢谢了.明天结帖