评论会员:游客 时间:2012/02/07
海诺Zunzer:得到的结果是,你正在寻找可以使用工会codeprespanclass="code-keyword"select/spanname,pincode,a1,b1,c,d,manymorespanclass="code-keyword"from/spantabspanclass="code-keyword"union/spanspanclass="code-keyword"all/spanspanclass="code-keyword"select/spanname,pincode,a2,b2,c,d,manymorespanclass="code-keyword"from/spantabspanclass="code-keyword"union/spanspanclass="code-keyword"all/spanspanclass="code-keyword"select/spanname,pincode,a3,b3,c,d,manymorespanclass="code-keyword"from/spantab/pre/code工会在这里imgsrc=但最好的情况下仍然会改变表(S)的结构
的SP蒂瓦里
评论会员:游客 时间:2012/02/07
codeprelang="sql"//firstUNPIVOTspanclass="code-keyword"table/spanspanclass="code-keyword"with/spanonespanclass="code-keyword"column/span[a]spanclass="code-keyword"for/span[a1][a2][a3] spanclass="code-keyword"select/span[name],[Pincode][a],spanclass="code-keyword"identity/span(spanclass="code-keyword"int/span,spanclass="code-digit"1/span,spanclass="code-digit"1/span)spanclass="code-keyword"as/spanPkspanclass="code-keyword"into/span#Temp1spanclass="code-keyword"from/span(spanclass="code-keyword"select/spantbl1.[Pincode],tbl1.[name],tbl1.[a1],tbl1.[a2],tbl1.[a3]spanclass="code-keyword"from/spantablenamespanclass="code-keyword"as/spantbl1)spanclass="code-keyword"as/spanupUNPIVOT([a]spanclass="code-keyword"for/span[tempval]spanclass="code-keyword"in/span([a1],[a2],[a3]))spanclass="code-keyword"as/spanpvt //secondUNPIVOTspanclass="code-keyword"table/spanspanclass="code-keyword"with/spanonespanclass="code-keyword"column/span[b]spanclass="code-keyword"for/span[b1][b2][b3] spanclass="code-keyword"select/span[Pincode],[name],[b],spanclass="code-keyword"IDENTITY/span(spanclass="code-keyword"int/span,spanclass="code-digit"1/span,spanclass="code-digit"1/span)spanclass="code-keyword"as/spanpkspanclass="code-keyword"into/span#Temp2spanclass="code-keyword"from/span(spanclass="code-keyword"select/spantbl2.[Pincode],tbl2.[name],tbl2.[b1],tbl2.[b2],tbl2.[b3]spanclass="code-keyword"from/spantablenamespanclass="code-keyword"as/spantbl2)spanclass="code-keyword"as/spanupUNPIVOT([b]spanclass="code-keyword"for/span[tempval]spanclass="code-keyword"in/span([b1],[b2],[b3]))spanclass="code-keyword"as/spanunpvt //thiswillbrtheoutputspanclass="code-keyword"of/spanthebothabovespanclass="code-keyword"table/span spanclass="code-keyword"select/spantemptable1.pk,temptable1.[name],temptable1.[pincode],temptable1.a,temptable2.b,spanclass="code-keyword"into/span#Temp3spanclass="code-keyword"from/span#Temp1temptable1spanclass="code-keyword"inner/spanspanclass="code-keyword"join/span#Temp2temptable2spanclass="code-keyword"on/spantemptable1.pk=temptable2.pk //thelasttabllhastheproperrecordspanclass="code-string""/spanspanclass="code-string"#Temp3"/span/pre/code
| OriginalGriff:我怀疑,最简单,最维护的方式做到这一点,要么是:
1)更改你的表结构,使你有两个表:
dataRecord [A] [B] [C] [D] ...
userRecord [名称] [PIN代码] [DATA1] [data2中] [data3] [data4]
那么你就可以使用更理解的结构来访问您的数据。
2)创建一个存储过程,创建一个临时表和部分把它分解成你的记录。然后返回临时表的内容。
个人 - 我会去选择(1)如果我能