一个交叉表

来源：互联网发布：安装php 编辑：程序博客网时间：2024/06/06 03:37

一个交叉表.用字段明做为值:原表数据为:
字段1   字段2   字段3
A1           B1           C1
A2           B2           C2
变换后:
COL1         COL2        COL3
字段1           A1                A2
字段2            B1                B2
字段3            C1                C2

createtabletablename(字段1varchar(100),字段2varchar(100),字段3varchar(100))
inserttablenameselect'gsm900/1800mhz/gprs', '85*44*21mm','80'
unionallselect'gsm900/1800mhz/gprs','82*46*21.5mm','79'
go

SELECT  top0
字段名=a.name
intoabc
FROMsyscolumnsa
            innerjoinsysobjectsdona.id=d.id
leftjoinsyspropertiesfond.id=f.idandf.smallid=0
where   d.xtype='U'and d.name='tablename'
orderbya.id,a.colorder

SELECT  top0
note=a.name
intoabcd
FROMsyscolumnsa
            innerjoinsysobjectsdona.id=d.id
leftjoinsyspropertiesfond.id=f.idandf.smallid=0
where   d.xtype='U' --表示用户类型
           and d.name='tablename'  --需要获取字段的表
orderbya.id,a.colorder

declare@numint,@Colvarchar(50),@Namevarchar(200), @Name1 varchar(200),@countint,@num1varchar(5)
select@num=1

select@count=count(*)fromtablename

while (@count>0)
begin
   select@Col='Col'+convert(varchar,@num)
   exec('ALTERTABLE abcadd['+@Col+'] varchar(200) NULL')
   select@num=@num+1
   set @count=@count-1
end

DECLAREAddNameColumns_CursorINSENSITIVECURSOR                    --取字段值                                        1<script type="text/javascript"></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>