求帮忙 求帮忙

[字朕名君]

实现tb_dataplat 与 pp_craftdata 两表数据相互转换。把tb_dataplat的data字段的值转化成pp_craftdata 的[StandardValue]的值，也就是说，把tb_dataplat一行100列，变成pp_craftdata的100行数据。
pp_craftdata的[DataPlatID]字段存储tb_dataplat表的主键[DataID] 。
tb_dataplat 是横向数据
pp_craftdata 是竖向数据

[字朕名君]

USE [erp_zfym] GO /****** 对象: Table [dbo].[TB_DataPlat] 脚本日期: 08/26/2013 11:23:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TB_DataPlat]( [DataID] [int] NOT NULL, [PFKID] [int] NULL, [UseType] [int] NULL, [DataSign] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL, [Data] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data1] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data2] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data3] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data4] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data5] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data6] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data7] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data8] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data9] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data10] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data11] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data12] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data13] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data14] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data15] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data16] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data17] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data18] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data19] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data20] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data21] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data22] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data23] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data24] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data25] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data26] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data27] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data28] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data29] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data30] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data31] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data32] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data33] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data34] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data35] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data36] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data37] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data38] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data39] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data40] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data41] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data42] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data43] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data44] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data45] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data46] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data47] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data48] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data49] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data50] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data51] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data52] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data53] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data54] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data55] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data56] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data57] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data58] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data59] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data60] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data61] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data62] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data63] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data64] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data65] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data66] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data67] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data68] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data69] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data70] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data71] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data72] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data73] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data74] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data75] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data76] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data77] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data78] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data79] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data80] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data81] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data82] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data83] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data84] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data85] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data86] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data87] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data88] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data89] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data90] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data91] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data92] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data93] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data94] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data95] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data96] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data97] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data98] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data99] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [Data100] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_TB_DataPlat] PRIMARY KEY CLUSTERED ( [DataID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF USE [erp_zfym] GO /****** 对象: Table [dbo].[PP_CraftData] 脚本日期: 08/26/2013 11:24:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PP_CraftData]( [CraftDataID] [int] IDENTITY(1,1) NOT NULL, [DataPlatID] [int] NULL, [CraftID] [int] NULL, [ProcessID] [int] NULL, [GoodsID] [int] NULL, [ProductID] [int] NULL, [ProductCode] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL, [Machine] [int] NULL, [StandardValue] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [Remark] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [OrderNO] [int] NULL, [Flag] [char](1) COLLATE Chinese_PRC_CI_AS NULL, [RelateID] [int] NULL, [ParentID] [int] NULL, [OpID] [int] NULL, [OpDate] [datetime] NULL, [CraftType] [int] NULL, CONSTRAINT [PK_PP_CraftData] PRIMARY KEY CLUSTERED ( [CraftDataID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF

[Andy__Huang]

declare @s varchar(max) set @s='' select @s=@s+'insert into PP_CraftData(StandardValue) select '+name+' from TB_DataPlat'+CHAR(13) from syscolumns where id=object_id('TB_DataPlat') and isnumeric(replace(name,'Data',''))=1 order by colid print @s 结果： /* insert into PP_CraftData(StandardValue) select Data1 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data2 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data3 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data4 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data5 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data6 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data7 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data8 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data9 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data10 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data11 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data12 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data13 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data14 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data15 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data16 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data17 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data18 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data19 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data20 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data21 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data22 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data23 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data24 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data25 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data26 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data27 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data28 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data29 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data30 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data31 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data32 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data33 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data34 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data35 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data36 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data37 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data38 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data39 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data40 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data41 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data42 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data43 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data44 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data45 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data46 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data47 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data48 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data49 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data50 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data51 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data52 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data53 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data54 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data55 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data56 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data57 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data58 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data59 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data60 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data61 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data62 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data63 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data64 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data65 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data66 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data67 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data68 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data69 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data70 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data71 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data72 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data73 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data74 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data75 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data76 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data77 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data78 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data79 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data80 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data81 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data82 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data83 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data84 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data85 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data86 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data87 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data88 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data89 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data90 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data91 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data92 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data93 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data94 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data95 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data96 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data97 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data98 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data99 from TB_DataPlat insert into PP_CraftData(StandardValue) select Data100 from TB_DataPlat */ 如果有where条件，你就自己加进去

[Shawn]

INSERT INTO TB_DataPlat(DataID, [data1],[data2],[data3],[data4],[data5],[data6],[data7],[data8],[data9],[data10],[data11],[data12],[data13],[data14],[data15],[data16],[data17],[data18],[data19],[data20],[data21],[data22],[data23],[data24],[data25],[data26],[data27],[data28],[data29],[data30],[data31],[data32],[data33],[data34],[data35],[data36],[data37],[data38],[data39],[data40],[data41],[data42],[data43],[data44],[data45],[data46],[data47],[data48],[data49],[data50],[data51],[data52],[data53],[data54],[data55],[data56],[data57],[data58],[data59],[data60],[data61],[data62],[data63],[data64],[data65],[data66],[data67],[data68],[data69],[data70],[data71],[data72],[data73],[data74],[data75],[data76],[data77],[data78],[data79],[data80],[data81],[data82],[data83],[data84],[data85],[data86],[data87],[data88],[data89],[data90],[data91],[data92],[data93],[data94],[data95],[data96],[data97],[data98],[data99],[data100])
SELECT 1, 'value1','value2','value3','value4','value5','value6','value7','value8','value9','value10','value11','value12','value13','value14','value15','value16','value17','value18','value19','value20','value21','value22','value23','value24','value25','value26','value27','value28','value29','value30','value31','value32','value33','value34','value35','value36','value37','value38','value39','value40','value41','value42','value43','value44','value45','value46','value47','value48','value49','value50','value51','value52','value53','value54','value55','value56','value57','value58','value59','value60','value61','value62','value63','value64','value65','value66','value67','value68','value69','value70','value71','value72','value73','value74','value75','value76','value77','value78','value79','value80','value81','value82','value83','value84','value85','value86','value87','value88','value89','value90','value91','value92','value93','value94','value95','value96','value97','value98','value99','value100'

--sql:
INSERT INTO PP_CraftData(DataPlatID, StandardValue)
SELECT DataID,value FROM  TB_DataPlat a
UNPIVOT	--注意:如果字段值为NULL,unpiovt转换时，不会生成相应的行。可给100个列加个default('')约束即可。
(value FOR field IN([data1],[data2],[data3],[data4],[data5],[data6],[data7],[data8],[data9],[data10],[data11],[data12],[data13],[data14],[data15],[data16],[data17],[data18],[data19],[data20],[data21],[data22],[data23],[data24],[data25],[data26],[data27],[data28],[data29],[data30],[data31],[data32],[data33],[data34],[data35],[data36],[data37],[data38],[data39],[data40],[data41],[data42],[data43],[data44],[data45],[data46],[data47],[data48],[data49],[data50],[data51],[data52],[data53],[data54],[data55],[data56],[data57],[data58],[data59],[data60],[data61],[data62],[data63],[data64],[data65],[data66],[data67],[data68],[data69],[data70],[data71],[data72],[data73],[data74],[data75],[data76],[data77],[data78],[data79],[data80],[data81],[data82],[data83],[data84],[data85],[data86],[data87],[data88],[data89],[data90],[data91],[data92],[data93],[data94],[data95],[data96],[data97],[data98],[data99],[data100])) b

/*
(100 行受影响)
*/

[字朕名君]

能用触发器 做一下吗