实现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 是竖向数据数据存储大神
pp_craftdata的[DataPlatID]字段存储tb_dataplat表的主键[DataID] 。
tb_dataplat 是横向数据
pp_craftdata 是竖向数据数据存储大神
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 OFFUSE [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,
[Re] [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
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条件,你就自己加进去
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 行受影响)
*/