22,207
社区成员
发帖
与我相关
我的任务
分享
DECLARE @table table(ID INT,Name VARCHAR(30),Title VARCHAR(50),Contact VARCHAR(50),Phone VARCHAR(50),Remark VARCHAR(500))
INSERT INTO @table
SELECT 1,'abc','123','张山','123456789','we' UNION ALL
SELECT 2,'abc','234','张三','123456789','we'
--想得到这样的结果
/*
ColumnName Value1 Value2
---------- ------ ------
Title 123 234
Contact 张山 张三
*/
--假如@table 表中有几十上百个字段怎么实现?
DECLARE @table table(ID INT,Name VARCHAR(30),
Title VARCHAR(50),Contact VARCHAR(50),Phone VARCHAR(50),Remark VARCHAR(500))
INSERT INTO @table
SELECT 1,'abc','123','张山','123456789','we' UNION ALL
SELECT 2,'abc','234','张三','123456789','we'
select ColumnName ='Title',* from(
SELECT 'Value'+LTRIM(ID)as value,Title FROM @table)t
pivot (max([Title]) for value in([Value1],[Value2]))s
union all
select ColumnName ='Contact',* from(
SELECT 'Value'+LTRIM(ID)as value,Contact FROM @table)t
pivot (max(Contact) for value in([Value1],[Value2]))s
/*
ColumnName Value1 Value2
Title 123 234
Contact 张山 张三
*/