求帮忙 求帮忙
实现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 是竖向数据 数据 存储 大神
[解决办法]
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条件，你就自己加进去
[解决办法]
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 行受影响)
*/
