27,582
社区成员




create table tb([pYear] int,[no] int,[pClass] varchar(1),[pCount] int)
insert tb
select 2007,3,'A',2 union all
select 2008,3,'A',1 union all
select 2008,4,'A',9 union all
select 2009,3,'A',3 union all
select 2009,4,'A',2 union all
select 2006,3,'B',1 union all
select 2007,3,'B',3 union all
select 2008,4,'B',6 union all
select 2009,3,'B',4
declare @s varchar(10)
select @s=isnull(@s+',','')+[pClass] from (select distinct [pClass] from tb) m
exec
('
declare @n int
set @n=3
select * from (select [pYear],[pClass] ,[pCount] from tb where no=@n ) m
pivot
(
sum([pCount]) for [pClass] in ( '+@s+')
) p')
/*
pYear A B
----------- ----------- -----------
2006 NULL 1
2007 2 3
2008 1 NULL
2009 3 4
(4 行受影响)
*/
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 19:55:49
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (pYear int,no int,pClass varchar(1),pCount int)
INSERT INTO [tb]
SELECT 2007,3,'A',2 UNION ALL
--SELECT 2008,3,'A',1 UNION ALL
SELECT 2008,4,'A',9 UNION ALL
SELECT 2009,3,'A',3 UNION ALL
SELECT 2009,4,'A',2 UNION ALL
SELECT 2006,3,'B',1 UNION ALL
SELECT 2007,3,'B',3 UNION ALL
SELECT 2008,4,'B',6 UNION ALL
SELECT 2009,3,'B',4
--SQL查询如下:
DECLARE @no int;
SET @no = 3;
DECLARE @str varchar(8000);
SET @str = ''
SELECT @str = @str + ',SUM(CASE WHEN pClass='''+pClass+''' THEN B.pCount ELSE 0 END) AS ['+pClass+']'
FROM tb
GROUP BY pClass;
SET @str = 'SELECT A.pYear'+@str+'
FROM(SELECT DISTINCT pYear FROM tb) AS A
LEFT JOIN (SELECT * FROM tb WHERE no='+RTRIM(@no)+') AS B
ON A.pYear = B.pYear
GROUP BY A.pYear';
EXEC(@str)
/*
pYear A B
----------- ----------- -----------
2006 0 1
2007 2 3
2008 0 0
2009 3 4
(4 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pYear] int,[no] int,[pClass] varchar(1),[pCount] int)
insert [tb]
select 2007,3,'A',2 union all
select 2008,3,'A',1 union all
select 2008,4,'A',9 union all
select 2009,3,'A',3 union all
select 2009,4,'A',2 union all
select 2006,3,'B',1 union all
select 2007,3,'B',3 union all
select 2008,4,'B',6 union all
select 2009,3,'B',4
declare @sql varchar(800)
select
@sql=isnull(@sql+',','')
+'sum(case when pClass='''+pClass+''' then pCount else 0 end) as ['+pClass+']'
from
(select distinct pClass from tb) t
set @sql='select pYear,no,'+@sql+' from tb group by pYear,no'
exec (@sql)
--测试结果:
/*
pYear no A B
----------- ----------- ----------- -----------
2006 3 0 1
2007 3 2 3
2008 3 1 0
2009 3 3 4
2008 4 9 6
2009 4 2 0
(6 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-17 19:48:42
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pYear] int,[no] int,[pClass] varchar(1),[pCount] int)
insert [tb]
select 2007,3,'A',2 union all
select 2008,3,'A',1 union all
select 2008,4,'A',9 union all
select 2009,3,'A',3 union all
select 2009,4,'A',2 union all
select 2006,3,'B',1 union all
select 2007,3,'B',3 union all
select 2008,4,'B',6 union all
select 2009,3,'B',4
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select pYear '
select @sql = @sql + ' , sum(case pClass when ''' + ltrim(pClass) + ''' then pCount else 0 end) [' + pClass + ']'
from (select distinct pClass from tb) as a
set @sql = @sql + ' from tb where NO=3 group by pYear '
exec(@sql)
----------------结果----------------------------
/* pYear A B
----------- ----------- -----------
2006 0 1
2007 2 3
2008 1 0
2009 3 4
(4 行受影响)
*/
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-17 19:55:49
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (pYear int,no int,pClass varchar(1),pCount int)
INSERT INTO [tb]
SELECT 2007,3,'A',2 UNION ALL
SELECT 2008,3,'A',1 UNION ALL
SELECT 2008,4,'A',9 UNION ALL
SELECT 2009,3,'A',3 UNION ALL
SELECT 2009,4,'A',2 UNION ALL
SELECT 2006,3,'B',1 UNION ALL
SELECT 2007,3,'B',3 UNION ALL
SELECT 2008,4,'B',6 UNION ALL
SELECT 2009,3,'B',4
--SQL查询如下:
DECLARE @no int;
SET @no = 3;
DECLARE @str varchar(8000);
SET @str = ''
SELECT @str = @str + ',SUM(CASE WHEN pClass='''+pClass+''' THEN pCount ELSE 0 END) AS ['+pClass+']'
FROM tb
GROUP BY pClass;
SET @str = 'SELECT pYear'+@str+' FROM tb WHERE no='+RTRIM(@no)+' GROUP BY pYear';
EXEC(@str)
/*
pYear A B
----------- ----------- -----------
2006 0 1
2007 2 3
2008 1 0
2009 3 4
(4 行受影响)
*/
declare @sql varchar(8000)
set @sql = 'select pYear '
select @sql = @sql + ' , sum(case pClass when ''' + ltrim(pClass) + ''' then 1 else 0 end) [' + pClass + ']'
from (select distinct pClass from tb) as a
set @sql = @sql + ' from tb where NO=3 group by pYear '
print(@sql)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(PYEAR INT,[NO] INT,PCLASS VARCHAR(50),PCOUNT INT)
GO
INSERT INTO TB
SELECT '2007', 3, 'A', 2 UNION ALL
SELECT '2008', 3, 'A', 1 UNION ALL
SELECT '2008', 4, 'A', 9 UNION ALL
SELECT '2009', 3, 'A', 3 UNION ALL
SELECT '2009', 4, 'A', 2 UNION ALL
SELECT '2006', 3, 'B', 1 UNION ALL
SELECT '2007', 3, 'B', 3 UNION ALL
SELECT '2008', 4, 'B', 6 UNION ALL
SELECT '2009', 3, 'B', 4
DECLARE @NO INT
SET @NO=3
DECLARE @STR VARCHAR(8000)
SELECT @STR=ISNULL(@STR+',SUM(CASE WHEN PCLASS=''','SELECT PYEAR,SUM(CASE WHEN PCLASS=''')
+PCLASS+''' THEN PCOUNT ELSE 0 END) '''+PCLASS+''''
FROM TB GROUP BY PCLASS
SELECT @STR=@STR+'FROM TB
WHERE [NO]='+CAST(@NO AS VARCHAR(10))+'
GROUP BY PYEAR'
EXEC (@STR)
/*
2006 0 1
2007 2 3
2008 1 0
2009 3 4
*/
CREATE PROC P(@NO INT)
AS
BEGIN
SELECT pYear,
MAX(CASE WHEN pClass='A' THEN PCOUNT ELSE 0 END) AS 'A',
MAX(CASE WHEN pClass='B' THEN PCOUNT ELSE 0 END) AS 'B'
FROM TB WHERE NO=@nO GROUP BY PYEAR
END