求Sql语句

Question

表A
dateStart    dateEnd    preBalance    income    expend    endBalance
2012-07-01    2012-07-15      0          100      50          50求下面效果的Sql语句（动态的Sql语句）
dateStart    dateEnd           A            B     
2012-07-01    2012-07-15     preBalance     0          
2012-07-01    2012-07-15     income        100      
2012-07-01    2012-07-15     expend         50          
2012-07-01    2012-07-15     endBalance     50

Answer 1

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([dateStart] DATETIME,[dateEnd] DATETIME,[preBalance] INT,[income] INT,[expend] INT,[endBalance] INT)
INSERT [tb]
SELECT '2012-07-01','2012-07-15',0,100,50,50
GO--> 测试语句：declare @s varchar(8000)
select @s=isnull(@s+',','')+quotename(Name)
from sys.columns where object_id=object_id('tb') and Name not in('dateStart','dateEnd') 
order by column_id
exec('select [dateStart],[dateEnd],[a],[b] from [tb] unpivot ([b] for [a] in('+@s+'))b')
/*
dateStart               dateEnd                 a                                                                                                                                b
----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 preBalance                                                                                                                       0
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 income                                                                                                                           100
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 expend                                                                                                                           50
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 endBalance                                                                                                                       50(4 行受影响)
*/

Answer 2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([dateStart] DATETIME,[dateEnd] DATETIME,[preBalance] INT,[income] INT,[expend] INT,[endBalance] INT)
INSERT [tb]
SELECT '2012-07-01','2012-07-15',0,100,50,50
GO--> 测试语句：
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [dateStart],[dateEnd],[a]='+quotename(name)
+',[b]='+quotename(name)+' from  [tb]'
from sys.columns where object_id=object_id('tb') and Name not in('dateStart','dateEnd') 
order by column_id
exec('select * from ('+@s+')t')
/*
dateStart               dateEnd                 a           b
----------------------- ----------------------- ----------- -----------
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 0           0
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 100         100
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 50          50
2012-07-01 00:00:00.000 2012-07-15 00:00:00.000 50          50(4 行受影响)
*/

Answer 3

--> 测试语句：
declare @s nvarchar(4000)
select @s=isnull(@s+char(13)+'union all '+char(13),'')+'select [dateStart],[dateEnd],[a]='+quotename(name,'''')+',[b]='+quotename(name)+' from [#tb]'
from tempdb.sys.columns where [object_id]=object_id('tempdb.dbo.#tb') and name not in('dateStart','dateEnd') 
order by column_id
--print @s
exec(@s)