怎么让它显示为这个格式的
3 个解决方案
#1
张三、李四要作为列名吗？
#2
用行转列，关键字，PIVOT 、UNPIVOT
#3
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
go
CREATE TABLE t([姓名] varchar(10),[n1] INT,[n2] INT)
INSERT INTO t values('张三',8,9)
INSERT INTO t values('李四',8,9)
select * from t
declare @rows varchar(8000)
select @rows=stuff((select ','+姓名 from t for xml path('')),1,1,'')
print @rows
declare @title varchar(8000)
select @title=isnull(@title+',','')+QUOTENAME(name) from syscolumns where name!='姓名' and id=OBJECT_ID('t')
print @title
declare @sql varchar(8000)
set @sql='select * from(select 姓名,[number],[值] from t unpivot([值] for [number] in ('+@title+')) a)b pivot(max([值]) for [姓名] in ('+@rows+'))c'
exec(@sql)
这样行吗？
number 张三 李四
n1 8 8
n2 9 9
#1
张三、李四要作为列名吗？
#2
用行转列，关键字，PIVOT 、UNPIVOT
#3
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
go
CREATE TABLE t([姓名] varchar(10),[n1] INT,[n2] INT)
INSERT INTO t values('张三',8,9)
INSERT INTO t values('李四',8,9)
select * from t
declare @rows varchar(8000)
select @rows=stuff((select ','+姓名 from t for xml path('')),1,1,'')
print @rows
declare @title varchar(8000)
select @title=isnull(@title+',','')+QUOTENAME(name) from syscolumns where name!='姓名' and id=OBJECT_ID('t')
print @title
declare @sql varchar(8000)
set @sql='select * from(select 姓名,[number],[值] from t unpivot([值] for [number] in ('+@title+')) a)b pivot(max([值]) for [姓名] in ('+@rows+'))c'
exec(@sql)
这样行吗？
number 张三 李四
n1 8 8
n2 9 9