# 一個交叉表

欄位1    欄位2    欄位3
A1            B1            C1
A2            B2            C2

COL1          COL2         COL3

create table tablename (欄位1 varchar(100),欄位2 varchar(100),欄位3 varchar(100))
insert tablename select 'gsm900/1800mhz/gprs',  '85*44*21mm', '80'
union all select 'gsm900/1800mhz/gprs' ,'82*46*21.5mm', '79'
go

SELECT   top 0
欄位名=a.name
into abc
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where    d.xtype='U' and  d.name = 'tablename'
order by a.id,a.colorder

SELECT   top 0
note =a.name
into abcd
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where    d.xtype='U'  --表示使用者類型
and  d.name = 'tablename'   -- 需要擷取欄位的表
order by a.id,a.colorder

declare @num int , @Col varchar(50) , @Name varchar(200),  @Name1  varchar(200) , @count int , @num1 varchar(5)
select @num = 1

select @count = count(*) from tablename

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

FOR
SELECT
filedname = a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where    d.xtype='U' and  d.name = 'tablename'
order by a.id,a.colorder

BEGIN
FETCH NEXT FROM AddNameColumns_Cursor INTO @Name  --對應相應欄位值
WHILE @@FETCH_STATUS = 0
BEGIN

insert into abc (欄位名)
select fname = @Name

delete abcd
exec (' insert into abcd (note) select name = [' + @Name +']  from tablename ')
select @num = 1
FOR select  note   from abcd
BEGIN
FETCH NEXT FROM AddNameColumns_Cursor1  INTO @Name1
WHILE @@FETCH_STATUS = 0
BEGIN

select @num1 =  convert(varchar, @num)

exec('update abc  set [Col'+ @num1 + '] = ''' + @Name1+'''   where  欄位名='''+  @Name +'''')

print  'update abc  set [' + @Col + '] = ''' + @Name1+'''   where  欄位名='''+  @Name +''''

select @num = @num +1

FETCH NEXT FROM AddNameColumns_Cursor1 INTO @Name1
END
END

FETCH NEXT FROM AddNameColumns_Cursor INTO @Name
END
END

select * from abc
SELECT * FROM tablename

drop table tablename
drop table abc
drop table abcd

## Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

## Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

## Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD