急！诚心请教！帮写一段交叉表的查询语句。在线等

Question

表        id            公司名     数量            日期
           1              A          10           1999-1-1
           2              B          10           1999-2-2
           3              A          10           1999-3-3
           4              C          10           1999-4-5
           5              C          10           1999-5-5
           6              B          10           1999-6-6
           7              D          10           2000-1-1
           8              E          10           2000-2-2执行查询语句显示
日期格式为1999-1以下是简写
公司名 0901  0902   0903  0904   0905   0906 ..... 总计  平均   0001  0002..  总计     平均
A       10           10                             20   1.7                   0        0         
B       10                               10         20   1.7                   0        0 
C                          10     10                20   1.7                   0        0
D                                                    0    0      10            10       0.8 
E                                                    0    0             10     10       0.8          

Answer 1

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-23 12:28:03
-------------------------------------
 
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (id INT,公司名 VARCHAR(1),数量 INT,日期 DATETIME)
INSERT INTO #tb
SELECT 1,'A',10,'1999-1-1' UNION ALL
SELECT 2,'B',10,'1999-2-2' UNION ALL
SELECT 3,'A',10,'1999-3-3' UNION ALL
SELECT 4,'C',10,'1999-4-5' UNION ALL
SELECT 5,'C',10,'1999-5-5' UNION ALL
SELECT 6,'B',10,'1999-6-6' UNION ALL
SELECT 7,'D',10,'2000-1-1' UNION ALL
SELECT 8,'E',10,'2000-2-2'--SQL查询如下:DECLARE @str VARCHAR(MAX);
SET @str = '';DECLARE @temp TABLE(id int identity,[Year] INT);
INSERT @temp([Year])
    SELECT DISTINCT YEAR(日期) FROM #tb;DECLARE @year INT,@id INT;
SET @year = (SELECT TOP 1 [Year] FROM @temp ORDER BY id);
SET @id = 0;WHILE @year IS NOT NULL
   BEGIN
        SELECT
            @str = @str + ',SUM(CASE WHEN CONVERT(VARCHAR(6),日期,112) = '''
                        + RTRIM(@year) + RIGHT(100 + number + 1,2) 
                        + ''' THEN 数量 ELSE 0 END) AS [' 
                        + RTRIM(@year) + RIGHT(100 + number + 1,2) + ']'
        FROM master.dbo.spt_values 
        WHERE type = 'p' AND number < 12;        SELECT 
            @str = @str + ',SUM(CASE WHEN YEAR(日期)=' + RTRIM(@year) 
                        + ' THEN 数量 ELSE 0 END) AS [' + RTRIM(@year) + '总计]'
                        + ',CAST(SUM(CASE WHEN YEAR(日期)=' + RTRIM(@year) 
                        + ' THEN 数量 ELSE 0 END) * 1.'
                        + '/ 12 AS NUMERIC(10,2)) AS [' 
                        + RTRIM(@year) + '平均]'        SET @id = @id + 1;
        SET @year = (SELECT TOP 1 [Year] FROM @temp WHERE id > @id ORDER BY id);
   ENDEXEC('SELECT 公司名' + @str + ' FROM #tb GROUP BY 公司名');/*
公司名  199901      199902      199903      199904      199905      199906      199907      199908      199909      199910      199911      199912      1999总计      1999平均                                  200001      200002      200003      200004      200005      200006      200007      200008      200009      200010      200011      200012      2000总计      2000平均
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
A    10          0           10          0           0           0           0           0           0           0           0           0           20          1.67                                    0           0           0           0           0           0           0           0           0           0           0           0           0           0.00
B    0           10          0           0           0           10          0           0           0           0           0           0           20          1.67                                    0           0           0           0           0           0           0           0           0           0           0           0           0           0.00
C    0           0           0           10          10          0           0           0           0           0           0           0           20          1.67                                    0           0           0           0           0           0           0           0           0           0           0           0           0           0.00
D    0           0           0           0           0           0           0           0           0           0           0           0           0           0.00                                    10          0           0           0           0           0           0           0           0           0           0           0           10          0.83
E    0           0           0           0           0           0           0           0           0           0           0           0           0           0.00                                    0           10          0           0           0           0           0           0           0           0           0           0           10          0.83(5 行受影响)
*/

Answer 2

create   table #2(id int,公司名  varchar(20),  数量 int, 日期 datetime)
insert into #2
select    1,               'A',          10,          '1999-1-1' union all
select    2,               'B',          10,          '1999-2-2' union all
select    3,               'A',          10,          '1999-3-3' union all
select    4,               'C',          10,          '1999-4-5'  union all
select    5,               'C',          10,          '1999-5-5' union all
select    6,               'B',          10,          '1999-6-6' union all
select    7,               'D',          10,          '2000-1-1' union all
select    8,               'E',          10,          '2000-2-2' declare @sql varchar(5000)
select @sql= isnull(@sql+',','')+'['+日期+']' from 
(select convert(varchar(6),日期,112) 日期 from #2 
union 
select convert(varchar(4),日期,112)+'-平均'  from #2 
union  
 select convert(varchar(4),日期,112)+'-总计' from #2    ) m  declare @sqlr varchar(1000)
set @sqlr=''
set @sqlr='select * from 
( select 公司名,数量,convert(varchar(6),日期,112)  日期    from #2  
  union all
select 公司名,sum(数量),convert(varchar(4),日期,112)+''-总计'' from #2 group by 公司名, convert(varchar(4),日期,112)
   union all
 select 公司名,cast(sum(数量)*1.0/12 as decimal(18,1)),convert(varchar(4),日期,112)+''-平均'' from #2 
group by 公司名, convert(varchar(4),日期,112) ) m
pivot
(
 max(数量) for 日期 in ('+@sql+')
)p'exec(@sqlr)
 

/*
公司名 199901 199902  199903 199904 199905 199906 1999-平均 1999-总计 200001  200002 2000-平均 2000-总计
A 10.0 NULL 10.0 NULL NULL NULL 1.7 20.0 NULL NULL NULL NULL
B NULL 10.0 NULL NULL NULL 10.0 1.7 20.0 NULL NULL NULL NULL
C NULL NULL NULL 10.0 10.0 NULL 1.7 20.0 NULL NULL NULL NULL
D NULL NULL NULL NULL NULL NULL NULL NULL 10.0 NULL 0.8 10.0
E NULL NULL NULL NULL NULL NULL NULL NULL NULL 10.0 0.8 10.0
(5 行受影响)*/

Answer 3

create   table #2(id int,公司名  varchar(20),  数量 int, 日期 datetime)
insert into #2
select    1,               'A',          10,          '1999-1-1' union all
select    2,               'B',          10,          '1999-2-2' union all
select    3,               'A',          10,          '1999-3-3' union all
select    4,               'C',          10,          '1999-4-5'  union all
select    5,               'C',          10,          '1999-5-5' union all
select    6,               'B',          10,          '1999-6-6' union all
select    7,               'D',          10,          '2000-1-1' union all
select    8,               'E',          10,          '2000-2-2' select top 12 id=identity(int,1,1) into #3 from sys.columnsdeclare @sql varchar(5000)
select @sql= isnull(@sql+',','')+'['+日期+']' from 
(select convert(varchar(6),日期,112) 日期 from (select 日期+right('0'+ltrim(id),2) 日期 
from (select distinct convert(varchar(4),日期,112) 日期 from #2 ) m , #3 ) m1 
union 
select convert(varchar(4),日期,112)+'-平均'  from #2 
union  
 select convert(varchar(4),日期,112)+'-总计' from #2    ) m   declare @sqlr varchar(1000)
set @sqlr=''
set @sqlr='select * from 
( select 公司名,数量,convert(varchar(6),日期,112)  日期    from #2  
  union all
select 公司名,sum(数量),convert(varchar(4),日期,112)+''-总计'' from #2 group by 公司名, convert(varchar(4),日期,112)
   union all
 select 公司名,cast(sum(数量)*1.0/12 as decimal(18,1)),convert(varchar(4),日期,112)+''-平均'' from #2 
group by 公司名, convert(varchar(4),日期,112) ) m
pivot
(
 max(数量) for 日期 in ('+@sql+')
)p'exec(@sqlr)drop table #2
drop table #3/*
公司名,时间,,,,,
A 10.0 NULL 10.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.7 20.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
B NULL 10.0 NULL NULL NULL 10.0 NULL NULL NULL NULL NULL NULL 1.7 20.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
C NULL NULL NULL 10.0 10.0 NULL NULL NULL NULL NULL NULL NULL 1.7 20.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
D NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 10.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0.8 10.0
E NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 10.0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0.8 10.0
*/

Answer 4

create   table #2(id int,公司名  varchar(20),  数量 int, 日期 datetime)
insert into #2
select    1,               'A',          10,          '1999-1-1' union all
select    2,               'B',          10,          '1999-2-2' union all
select    3,               'A',          10,          '1999-3-3' union all
select    4,               'C',          10,          '1999-4-5'  union all
select    5,               'C',          10,          '1999-5-5' union all
select    6,               'B',          10,          '1999-6-6' union all
select    7,               'D',          10,          '2000-1-1' union all
select    8,               'E',          10,          '2000-2-2' select top 12 id=identity(int,1,1) into #3 from sys.columnsdeclare @sql varchar(5000)
select @sql= isnull(@sql+',','')+'['+日期+']' from 
(select convert(varchar(6),日期,112) 日期 from (select 日期+right('0'+ltrim(id),2) 日期 
from (select distinct convert(varchar(4),日期,112) 日期 from #2 ) m , #3 ) m1 
union 
select convert(varchar(4),日期,112)+'-平均'  from #2 
union  
 select convert(varchar(4),日期,112)+'-总计' from #2    ) m   declare @sqlr varchar(1000)
set @sqlr=''
set @sqlr='select * from 
( select 公司名,数量,convert(varchar(6),日期,112)  日期    from #2  
  union all
select 公司名,sum(数量),convert(varchar(4),日期,112)+''-总计'' from #2 group by 公司名, convert(varchar(4),日期,112)
   union all
 select 公司名,cast(sum(数量)*1.0/12 as decimal(18,1)),convert(varchar(4),日期,112)+''-平均'' from #2 
group by 公司名, convert(varchar(4),日期,112) ) m
pivot
(
 max(数量) for 日期 in ('+@sql+')
)p'exec(@sqlr)drop table #2
drop table #3

Answer 5

所影响的行数为 8 行）服务器: 消息 170，级别 15，状态 1，行 12
第 12 行: 'pivot' 附近有语法错误。

Answer 6

sql server 2000?将我9楼的
declare @str varchar(max)
改为
declare @str varchar(8000)