--建立测试环境
Create Table 表(ID varchar(10),Name varchar(10),Value varchar(10))
--插入数据
insert into 表
select '1','标题','123' union
select '2','标题','456' union
select '3','标题','789' union
select '1','内容','asd' union
select '2','内容','fefe' union
select '3','内容','yjyj'
select * from 表
--测试语句
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',max(case Name when ''' + Name + ''' then Value else null end) [' + Name + ']'
from (select distinct Name from 表) a
set @sql = @sql + ' from 表 group by id'
print @sql
exec(@sql)
--删除测试环境
Drop Table 表--结果
ID 标题 内容
1 123 asd
2 456 fefe
3 789 yjyj
Create Table 表(ID varchar(10),Name varchar(10),Value varchar(10))
--插入数据
insert into 表
select '1','标题','123' union
select '2','标题','456' union
select '3','标题','789' union
select '1','内容','asd' union
select '2','内容','fefe' union
select '3','内容','yjyj'
select * from 表
--测试语句
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',max(case Name when ''' + Name + ''' then Value else null end) [' + Name + ']'
from (select distinct Name from 表) a
set @sql = @sql + ' from 表 group by id'
print @sql
exec(@sql)
--删除测试环境
Drop Table 表--结果
ID 标题 内容
1 123 asd
2 456 fefe
3 789 yjyj
(select id,value from table where name='标题') t1 inner join
(select id,value from table where name='内容') t2 on t1.id =t2.id如果你的NAME字段中有很多不同类型的值得话,用一条SQL语句是不行的
Select
ID,
(Select Value from TableName Where ID=A.ID And Name=N'标题') As 标题,
(Select Value from TableName Where ID=A.ID And Name=N'内容') As 内容
from TableName A
Group By ID
Order By ID
Declare @S Nvarchar(4000)
Set @S='Select ID '
Select @S=@S+' ,(Select Value from TEST Where ID=A.ID And Name=N'''+Name +''') As '+ Name
from (Select Distinct Name from TEST) A Order by Name
Select @S=@S+' from TEST A Group By ID Order By ID'
--Select @S
EXEC(@S)
insert into table_test(id,name,value) values (2,'标题', '456')
insert into table_test(id,name,value) values (3, '标题', '789')
insert into table_test(id,name,value) values (1, '内容', 'asd')
insert into table_test(id,name,value) values (2, '内容', 'fefe')
insert into table_test(id,name,value) values (3, '内容', 'yjyj')select a.id,a.value 标题,b.内容 from table_test a, (select id,value 内容 from table_test where name ='内容') b where a.id =b.id
and a.name ='标题'drop table table_test
Create Table TEST
(ID Int,
Name Nvarchar(10),
Value Varchar(50))
Insert TEST Values(1, N'标题', '123')
Insert TEST Values(2, N'标题', '456')
Insert TEST Values(3, N'标题', '789')
Insert TEST Values(1, N'内容', 'asd')
Insert TEST Values(2, N'内容', 'fefe')
Insert TEST Values(3, N'内容', 'yjyj')
--测试
Select
ID,
(Select Value from TEST Where ID=A.ID And Name=N'标题') As 标题,
(Select Value from TEST Where ID=A.ID And Name=N'内容') As 内容
from TEST A
Group By ID
Order By IDDeclare @S Nvarchar(4000)
Set @S='Select ID '
Select @S=@S+' ,(Select Value from TEST Where ID=A.ID And Name=N'''+Name +''') As '+ Name
from (Select Distinct Name from TEST) A Order by Name
Select @S=@S+' from TEST A Group By ID Order By ID'
--Select @S
EXEC(@S)
GO
--删除测试环境
Drop Table TEST
--结果
/*
ID 标题 内容
1 123 asd
2 456 fefe
3 789 yjyjID 内容 标题
1 asd 123
2 fefe 456
3 yjyj 789
*/
--建立测试环境
if object_id('test') is not null drop table test
go
Create Table TEST
(ID Int,
Name Nvarchar(10),
Value Varchar(50))
Insert TEST Values(1, N'标题', '123')
Insert TEST Values(2, N'标题', '456')
Insert TEST Values(3, N'标题', '789')
Insert TEST Values(1, N'内容', 'asd')
Insert TEST Values(2, N'内容', 'fefe')
Insert TEST Values(3, N'内容', 'yjyj')
--测试
declare @i int, @select varchar(400), @from varchar(4000)
select @select=' select t1.id as id',
@from=' from ',
@i=0
select
@i=@i+1,
@select=@select+', t'+convert(varchar(5), @i)+'.value as '+name,
@from=@from+' join (select * from test where name='
+char(0x27)+name+char(0x27)+')t'+convert(varchar(5), @i)
+' on t'+convert(varchar(5), @i-1)
+'.id=t'+convert(varchar(5), @i)+'.id '
from (select distinct name from test)tset @from=replace(@from, 'on t0.id=t1.id', '')
set @from=replace(@from, 'from join', 'from')exec(@select+@from)