继续请教

[koukoujiayi]

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

希望no是一个变量,根据不同的no得到以下结果:
例如如no=3
pYear A B
2006 0 1
2007 2 3
2008 1 0
2009 3 4

其中A,B,都不是固定的!
望指教!!十分感谢!!

[guguda2008]

行转列。。。。

[SQL77]

行转列呀,你在你前面的那个语句里加个条件NO=3就好了

[SQL77]

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

[lihan6415151528]

行转列，精华里面有

[koukoujiayi]

[Quote=引用 2 楼 sql77 的回复:]
行转列呀,你在你前面的那个语句里加个条件NO=3就好了
[/Quote]
好像不行哦!!

[koukoujiayi]

[Quote=引用 3 楼 sql77 的回复:]
SQL codeCREATEPROC P(@NOINT)ASBEGINSELECT pYear,MAX(CASEWHEN pClass='A'THEN PCOUNTELSE0END)AS'A',MAX(CASEWHEN pClass='B'THEN PCOUNTELSE0END)AS'B'FROM TBWHERE NO=@nOGROUPBY PYEAREND
[/Quote]
你这样A,B就固定了!!有可能只有A而没有B,也有可能有 C,D等!!

[SQL77]

[Quote=引用 6 楼 koukoujiayi 的回复:]
引用 3 楼 sql77 的回复:
SQL codeCREATEPROC P(@NOINT)ASBEGINSELECT pYear,MAX(CASEWHEN pClass='A'THEN PCOUNTELSE0END)AS'A',MAX(CASEWHEN pClass='B'THEN PCOUNTELSE0END)AS'B'FROM TBWHERE NO=@nOGROUPBY PYEAREND

你这样A,B就固定了!!有可能只有A而没有B,也有可能有 C,D等!!
[/Quote]
没看清题目,你用动态的,

[guguda2008]

其实我在想能不能做个行转列的代码自动生成程序

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
*/

[--小F--]

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) 

[liangCK]

-------------------------------------
--  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 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- 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 行受影响)
*/

[百年树人]

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 行受影响)

*/

[liangCK]

-------------------------------------
--  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 行受影响)
*/

[xiequan2]

 

 
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 行受影响)


*/

[devilidea]

学习