if object_id('tbTest') is not null
drop table tbTest
GO
create table tbTest(CardID varchar(10),Name varchar(10),Age int,[Date] int)
insert tbTest
select '001', '小王', 20, 2007 union all
select '002', '小张', 21, 2007 union all
select '003', '小赵', 22, 2007
----动态SQL
declare @sql1 varchar(8000)
set @sql1 = 'select ''CardID'''
select @sql1 = @sql1 + ',max(case CardID when ''' + CardID + ''' then CardID end)'
from tbTest order by CardID
declare @sql2 varchar(8000)
set @sql2 = 'select ''Name'''
select @sql2 = @sql2 + ',max(case CardID when ''' + CardID + ''' then Name end)'
from tbTest order by CardID
declare @sql3 varchar(8000)
set @sql3 = 'select ''Age'''
select @sql3 = @sql3 + ',max(case CardID when ''' + CardID + ''' then rtrim(Age) end)'
from tbTest order by CardID
declare @sql4 varchar(8000)
set @sql4 = 'select ''Date'''
select @sql4 = @sql4 + ',max(case CardID when ''' + CardID + ''' then rtrim([Date]) end)'
from tbTest order by CardIDset @sql1 = @sql1 + ' from tbTest
union all ' + @sql2 + ' from tbTest
union all ' + @sql3 + ' from tbTest
union all ' + @sql4 + ' from tbTest'
----执行
EXEC(@sql1)/*结果
------------------------------------------
CardID 001 002 003
Name 小王 小张 小赵
Age 20 21 22
Date 2007 2007 2007
*/
drop table tbTest
GO
create table tbTest(CardID varchar(10),Name varchar(10),Age int,[Date] int)
insert tbTest
select '001', '小王', 20, 2007 union all
select '002', '小张', 21, 2007 union all
select '003', '小赵', 22, 2007
----动态SQL
declare @sql1 varchar(8000)
set @sql1 = 'select ''CardID'''
select @sql1 = @sql1 + ',max(case CardID when ''' + CardID + ''' then CardID end)'
from tbTest order by CardID
declare @sql2 varchar(8000)
set @sql2 = 'select ''Name'''
select @sql2 = @sql2 + ',max(case CardID when ''' + CardID + ''' then Name end)'
from tbTest order by CardID
declare @sql3 varchar(8000)
set @sql3 = 'select ''Age'''
select @sql3 = @sql3 + ',max(case CardID when ''' + CardID + ''' then rtrim(Age) end)'
from tbTest order by CardID
declare @sql4 varchar(8000)
set @sql4 = 'select ''Date'''
select @sql4 = @sql4 + ',max(case CardID when ''' + CardID + ''' then rtrim([Date]) end)'
from tbTest order by CardIDset @sql1 = @sql1 + ' from tbTest
union all ' + @sql2 + ' from tbTest
union all ' + @sql3 + ' from tbTest
union all ' + @sql4 + ' from tbTest'
----执行
EXEC(@sql1)/*结果
------------------------------------------
CardID 001 002 003
Name 小王 小张 小赵
Age 20 21 22
Date 2007 2007 2007
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货