请教

[koukoujiayi]

有表是:
pYear pClass pCount
2007 A 2
2008 A 1
2009 A 3
2006 B 1
2007 B 3
2009 B 4

希望得到:
pYear A B
2006 0 1
2007 2 3
2008 1 0
2009 3 4

十分感谢!!

[soft_wsx]

行列转换用CASE WHEN ELSE END，
等我写一下！

[百年树人]

select 
  pYear,
  sum(case when pClass='A' then pCount else 0 end) as A,
  sum(case when pClass='B' then pCount else 0 end) as B
from
  tb
group by
  pYear

[ws_hgo]

select pYear,
sum(case when pClass then pCount else 0 end) 'A',
sum(case when pClass then pCount else 0 end) 'B'
from 表 group by pYear

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-17 14:47:14
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (pYear int,pClass varchar(1),pCount int)
INSERT INTO @tb
SELECT 2007,'A',2 UNION ALL
SELECT 2008,'A',1 UNION ALL
SELECT 2009,'A',3 UNION ALL
SELECT 2006,'B',1 UNION ALL
SELECT 2007,'B',3 UNION ALL
SELECT 2009,'B',4

--SQL查询如下:

SELECT pYear,ISNULL(A,0) AS A,ISNULL(B,0) AS B
FROM @tb
    PIVOT(MAX(pCount) FOR pClass IN(A,B)) AS unpvt

/*
pYear       A           B
----------- ----------- -----------
2006        0           1
2007        2           3
2008        1           0
2009        3           4

(4 行受影响)


*/

[htl258_Tony]

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-17 14:47:33
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
	Mar 29 2009 10:27:29 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([pYear] INT,[pClass] NVARCHAR(10),[pCount] INT)
INSERT [tb]
SELECT 2007,'A',2 UNION ALL
SELECT 2008,'A',1 UNION ALL
SELECT 2009,'A',3 UNION ALL
SELECT 2006,'B',1 UNION ALL
SELECT 2007,'B',3 UNION ALL
SELECT 2009,'B',4
GO
--SELECT * FROM [tb]

-->SQL查询如下:
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s,'select [pYear]')+',max(case [pClass] when '''+[pClass]+''' then [pCount] else 0 end)['+[pClass]+']'
FROM tb GROUP BY [pClass]
EXEC(@s+' from tb group by [pYear]')
/*
pYear       A           B
----------- ----------- -----------
2006        0           1
2007        2           3
2008        1           0
2009        3           4

(4 行受影响)
*/

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pYear] int,[pClass] varchar(1),[pCount] int)
insert [tb]
select 2007,'A',2 union all
select 2008,'A',1 union all
select 2009,'A',3 union all
select 2006,'B',1 union all
select 2007,'B',3 union all
select 2009,'B',4

select 
  pYear,
  sum(case when pClass='A' then pCount else 0 end) as A,
  sum(case when pClass='B' then pCount else 0 end) as B
from
  tb
group by
  pYear


--测试结果:
/*
pYear       A           B
----------- ----------- -----------
2006        0           1
2007        2           3
2008        1           0
2009        3           4

(4 行受影响)

*/

[soft_wsx]

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-17 14:50:42.920●●●●●
　★★★★★soft_wsx★★★★★
*/


if object_ID('TB') IS NOT NULL DROP TABLE TB
  create table tb(pYear nvarchar(4),    pClass nvarchar(2),    pCount int)
insert tb
select
'2007',      'A'    ,      2 union all select 
'2008',      'A'   ,       1 union all select 
'2009',      'A'     ,     3 union all select 
'2006' ,     'B'  ,        1 union all select 
'2007' ,     'B' ,         3 union all select 
'2009' ,     'B',          4 

希望得到: 
pYear      A          B 
2006      0          1 
2007      2          3 
2008      1          0 
2009      3          4 

--select * from tb

declare @sql nvarchar(4000)
SET @sql=N'select [pYear]'   --初始化变量必须
select @sql=@sql+N','+
                  QUOTENAME(pClass)+
                    N'=max(
                            case when [pClass]='+quotename(pClass,N'''')
                            +N' then pCount else 0 end)'
                    from tb group by pClass 
                     
set @sql=@sql+N' from tb group by pYear'
       print @sql 
exec(@sql)
/*
pYear	A	B
2006	0	1
2007	2	3
2008	1	0
2009	3	4
*/                 
                    
                    --动态生成的语句
select [pYear],[A]=max(
                            case when [pClass]='A' then pCount else 0 end),[B]=max(
                            case when [pClass]='B' then pCount else 0 end) from tb group by pYear

[soft_wsx]

太牛了！你们

[ws_hgo]

IF NOT OBJECT_ID('tb1') IS NULL
    DROP TABLE tb1

CREATE TABLE tb1([pYear] INT,[pClass] NVARCHAR(10),[pCount] INT)
INSERT tb1
SELECT 2007,'A',2 UNION ALL
SELECT 2008,'A',1 UNION ALL
SELECT 2009,'A',3 UNION ALL
SELECT 2006,'B',1 UNION ALL
SELECT 2007,'B',3 UNION ALL
SELECT 2009,'B',4

select pYear,
sum(case when pClass='A' then pCount else 0 end) 'A',
sum(case when pClass='B' then pCount else 0 end) 'B'
from tb1 group by pYear


pYear       A           B
----------- ----------- -----------
2006        0           1
2007        2           3
2008        1           0
2009        3           4

[koukoujiayi]

谢谢楼上的's
A,B希望不要写死!!

[htl258_Tony]

[Quote=引用 10 楼 koukoujiayi 的回复:]
谢谢楼上的's
A,B希望不要写死!!
[/Quote]
5 7 L

[wgsasd311]

IF NOT OBJECT_ID('tb') IS NULL
DROP TABLE tb

CREATE TABLE tb([pYear] INT,[pClass] NVARCHAR(10),[pCount] INT)
INSERT tb
SELECT 2007,'A',2 UNION ALL
SELECT 2008,'A',1 UNION ALL
SELECT 2009,'A',3 UNION ALL
SELECT 2006,'B',1 UNION ALL
SELECT 2007,'B',3 UNION ALL
SELECT 2009,'B',4

DECLARE @s VARCHAR(8000)
set @s=''
SELECT @s=@s+','+pClass+'=sum(case [pClass] when '''+[pClass]+''' then [pCount] else 0 end)'
FROM tb GROUP BY pClass
select @s='select pyear'+@s+' from tb group by pyear'
exec(@s)