继续请教

koukoujiayi 2009-09-17 07:47:06
有表是:
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,都不是固定的!
望指教!!十分感谢!!
...全文
177 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
devilidea 2009-09-17
  • 打赏
  • 举报
回复
学习
xiequan2 2009-09-17
  • 打赏
  • 举报
回复
 


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


*/
liangCK 2009-09-17
  • 打赏
  • 举报
回复
-------------------------------------
-- 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 行受影响)
*/
百年树人 2009-09-17
  • 打赏
  • 举报
回复
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 行受影响)

*/
--小F-- 2009-09-17
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行受影响)
*/
liangCK 2009-09-17
  • 打赏
  • 举报
回复
-------------------------------------
-- 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-- 2009-09-17
  • 打赏
  • 举报
回复
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)
guguda2008 2009-09-17
  • 打赏
  • 举报
回复
其实我在想能不能做个行转列的代码自动生成程序

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
*/
SQL77 2009-09-17
  • 打赏
  • 举报
回复
[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]
没看清题目,你用动态的,
koukoujiayi 2009-09-17
  • 打赏
  • 举报
回复
[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等!!
koukoujiayi 2009-09-17
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 sql77 的回复:]
行转列呀,你在你前面的那个语句里加个条件NO=3就好了
[/Quote]
好像不行哦!!
lihan6415151528 2009-09-17
  • 打赏
  • 举报
回复
行转列,精华里面有
SQL77 2009-09-17
  • 打赏
  • 举报
回复
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
SQL77 2009-09-17
  • 打赏
  • 举报
回复
行转列呀,你在你前面的那个语句里加个条件NO=3就好了
guguda2008 2009-09-17
  • 打赏
  • 举报
回复
行转列。。。。

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧