行转列

Question

系统有时会提出错误子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

Answer 1

DECLARE @cmd NVARCHAR(4000);
DECLARE @columns VARCHAR(8000);
SET @columns = '';
SELECT @columns=@columns +',(SELECT SUM(Qty) FROM #t WHERE ID=a.ID AND CONVERT(VARCHAR(7),InputDate,120)<='''
                    + CONVERT(VARCHAR(7),InputDate,120)+ ''') AS ['+CONVERT(VARCHAR(7),InputDate,120)+']'
FROM #t
GROUP BY CONVERT(VARCHAR(7),InputDate,120);SET @cmd=
    'SELECT  distinct ID'+ @columns +''     -----此处加上distinct
   +' FROM #t a GROUP BY a.ID,CONVERT(VARCHAR(7),a.InputDate,120)';
print @cmd
EXEC sp_executesql @cmd ; 
GO

Answer 2

CREATE TABLE tb(ID NVARCHAR(10),InputDate SMALLDATETIME,QTY INT)
INSERT INTO tb
SELECT 'a','2009-2-5',50 UNION ALL
SELECT 'a','2009-3-5',20 UNION ALL
SELECT 'a','2009-4-5',50 UNION ALL
SELECT 'b','2009-2-5',60 UNION ALL
SELECT 'b','2009-3-5',50 UNION ALL
SELECT 'b','2009-4-5',50 UNION ALL
SELECT 'c','2009-2-5',20 UNION ALL
SELECT 'c','2009-3-5',30 UNION ALL
SELECT 'c','2009-4-5',10 --静态SQL
select id,
  max(case convert(varchar(7),inputdate,120) when '2009-02' then qty else 0 end) [2009-02],
  max(case convert(varchar(7),inputdate,120) when '2009-03' then qty else 0 end) [2009-03],
  max(case convert(varchar(7),inputdate,120) when '2009-04' then qty else 0 end) [2009-04]
from
(
  select id,inputdate,qty=(select sum(qty) from tb where id = t.id and inputdate <= t.inputdate) from tb t
) m
group by id
/*
id         2009-02     2009-03     2009-04     
---------- ----------- ----------- ----------- 
a          50          70          120
b          60          110         160
c          20          50          60（所影响的行数为 3 行）
*/--动态SQL
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case convert(varchar(7),inputdate,120) when ''' + inputdate + ''' then qty else 0 end) [' + inputdate + ']'
from (select distinct convert(varchar(7),inputdate,120) inputdate from (select id,inputdate,qty=(select sum(qty) from tb where id = t.id and inputdate <= t.inputdate) from tb t)m) as a
set @sql = @sql + ' from (select id,inputdate,qty=(select sum(qty) from tb where id = t.id and inputdate <= t.inputdate) from tb t)m group by id'
exec(@sql) 
/*
id         2009-02     2009-03     2009-04     
---------- ----------- ----------- ----------- 
a          50          70          120
b          60          110         160
c          20          50          60（所影响的行数为 3 行）
*/drop table tb

Answer 3

这是测试数据,
原程序按你的修改也是有
系统有时会提出错误 子查询返回的值不止一个。当子查询跟随在 =、!=、 <、 <=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

Answer 4

上述动态可简化为:--动态SQL
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case convert(varchar(7),inputdate,120) when ''' + inputdate + ''' then qty else 0 end) [' + inputdate + ']'
from (select distinct convert(varchar(7),inputdate,120) inputdate from tb t) as a
set @sql = @sql + ' from (select id,inputdate,qty=(select sum(qty) from tb where id = t.id and inputdate <= t.inputdate) from tb t)m group by id'
exec(@sql) 

Answer 5

TO dawugui
你的这种应该要比那种嵌套查询要快一点吧.

Answer 6

换种方式:---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-02 20:15:55
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
CREATE TABLE tb(ID NVARCHAR(10),InputDate SMALLDATETIME,QTY INT)
INSERT tb
SELECT 'a','2009-2-5',50 UNION ALL
SELECT 'a','2009-3-5',20 UNION ALL
SELECT 'a','2009-4-5',50 UNION ALL
SELECT 'b','2009-2-5',60 UNION ALL
SELECT 'b','2009-3-5',50 UNION ALL
SELECT 'b','2009-4-5',50 UNION ALL
SELECT 'c','2009-2-5',20 UNION ALL
SELECT 'c','2009-3-5',30 UNION ALL
SELECT 'c','2009-4-5',10 --静态SQL
select id,
  sum(case when convert(varchar(7),inputdate,120)<='2009-02' then qty else 0 end) [2009-02],
  sum(case when convert(varchar(7),inputdate,120)<='2009-03' then qty else 0 end) [2009-03],
  sum(case when convert(varchar(7),inputdate,120)<='2009-04' then qty else 0 end) [2009-04]
from tb
group by id--动态SQL
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ', sum(case when convert(varchar(7),inputdate,120) <= ''' + inputdate + ''' then qty else 0 end) [' + inputdate + ']'
from (select distinct convert(varchar(7),inputdate,120) inputdate from tb) t
set @sql = @sql + ' from tb group by id'
exec(@sql) 
/*
id         2009-02     2009-03     2009-04
---------- ----------- ----------- -----------
a          50          70          120
b          60          110         160
c          20          50          60(3 行受影响)*/

Answer 7

SQL2005用pivot
CREATE TABLE #t(ID NVARCHAR(10),InputDate SMALLDATETIME,QTY INT)
INSERT INTO #t
SELECT 'a','2009-2-5',50 UNION ALL
SELECT 'a','2009-3-5',20 UNION ALL
SELECT 'a','2009-4-5',50 UNION ALL
SELECT 'b','2009-2-5',60 UNION ALL
SELECT 'b','2009-3-5',50 UNION ALL
SELECT 'b','2009-4-5',50 UNION ALL
SELECT 'c','2009-2-5',20 UNION ALL
SELECT 'c','2009-3-5',30 UNION ALL
SELECT 'c','2009-4-5',10 go
declare @s nvarchar(1000)
set @s=''
select @s=',['+convert(varchar(7),InputDate,120)+']'+@s from #T group by convert(varchar(7),InputDate,120) order by convert(varchar(7),InputDate,120) desc
set @s=stuff(@s,1,1,'')exec(
'with C
as
(
select 
ID,convert(varchar(7),InputDate,120) as InputDate,
(select sum(Qty) from #T where ID=a.ID and InputDate<=a.InputDate) as Qty
from #T  a
)
select  * from C  pivot (max(Qty) for InputDate in('+@s+'))b')
--or;with C
as
(
select 
ID,convert(varchar(7),InputDate,120) as InputDate,
(select sum(Qty) from #T where ID=a.ID and InputDate<=a.InputDate) as Qty
from #T  a
)
select  * from C  pivot (max(Qty) for InputDate in([2009-02],[2009-03],[2009-04]))b/*
ID         2009-02     2009-03     2009-04
---------- ----------- ----------- -----------
a          50          70          120
b          60          110         160
c          20          50          60(3 行受影响)
*/

Answer 8

CREATE TABLE #t(ID NVARCHAR(10),InputDate SMALLDATETIME,QTY INT)
INSERT INTO #t
SELECT 'a','2009-2-5',50 UNION ALL
SELECT 'a','2009-3-5',20 UNION ALL
SELECT 'a','2009-4-5',50 UNION ALL
SELECT 'b','2009-2-5',60 UNION ALL
SELECT 'b','2009-3-5',50 UNION ALL
SELECT 'b','2009-4-5',50 UNION ALL
SELECT 'c','2009-2-5',20 UNION ALL
SELECT 'c','2009-3-5',30 UNION ALL
SELECT 'c','2009-4-5',10 select id,
sum(case when convert(varchar(7),inputdate,120)='2009-02' then cnt else 0 end) '2009-2',
sum(case when convert(varchar(7),inputdate,120)='2009-03' then cnt else 0 end) '2009-3',
sum(case when convert(varchar(7),inputdate,120)='2009-04' then cnt else 0 end) '2009-4'
from
(
 select id,inputdate,(select sum(qty) from #t where id = t.id and inputdate <= t.inputdate) as cnt from #t t
)t1
group by id
id         2009-2      2009-3      2009-4
---------- ----------- ----------- -----------
a          50          70          120
b          60          110         160
c          20          50          60(3 行受影响)