求sql分组查询语句，高手赐教！

Question

数据表
productprodid  自动编号
prodname 产品名称
prodsize   尺码
prodcolor   颜色
prodstyle  款号
prodprice  价格
prodcode  条形码=款号+颜色+尺码添加数据
1  衣服   01   020  203   500   20302001
2  衣服   02   020  203   500   20302001
3  衣服   03   020  203   500   20302001
4  衣服   02   030  203   500   20302001
5  裤子   02   030  204   600   20302001
6  衣服   01   030  203   500   20302001
7  裤子   01   030  204   600   20302001
现在要根据款号进行分组因为
prodstyle      prodsize    prodcolor     prodprice    prodname
203             020,030     00.01,02        500        衣服
204            020,030      00.01,02        500         裤子
求高手赐教啊求高手赐教啊！！！！！！！！！！！！！！！！！！！！！！

Answer 1

select prodstyle ,prodsize ,prodcolor ,prodprice ,prodname 
from product
group by prodstyle ,prodsize ,prodcolor ,prodprice ,prodname  可以说的再明白点吗

Answer 2

就是说一个款号有很多个颜色 很多个  尺码有x m  l 每个颜色都有3个尺码
我需要统计 按照款号分组  比如203 204  205 三个款   每个有红色和黑色两个色号
那么产品表就是
203 红色   x 
203 红色   m
203 红色   L
204 黑色   x
以此类推我想统计结果
款号  颜色---------尺码    
203  红色，黑色   x，m，l
204  红色，黑色   x，m，l其他的价格和名称同一个款式都是一样的

Answer 3

--仅供参考
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
prodid int,
prodname char(5),
prodsize char(3),
prodcolor char(4),
prodstyle char(5),
prodprice int,
prodcode char(10)
)
go
--插入测试数据
insert into tb select 1,'衣服','01','020','203',500,'20302001'
union all select 2,'衣服','02','020','203',500,'20302001'
union all select 3,'衣服','03','020','203',500,'20302001'
union all select 4,'衣服','02','030','203',500,'20302001'
union all select 5,'裤子','02','030','204',600,'20302001'
union all select 6,'衣服','01','030','203',500,'20302001'
union all select 7,'裤子','01','030','204',600,'20302001'
go
--代码实现select distinct prodstyle
,prodsize=stuff((select ','+rtrim(prodsize) from (select distinct prodstyle,prodsize from tb)a where prodstyle=t.prodstyle for xml path('')),1,1,'')
,prodcolor=stuff((select ','+rtrim(prodcolor) from (select distinct prodstyle,prodcolor from tb)a where prodstyle=t.prodstyle for xml path('')),1,1,'')
,prodprice
,prodname
from tb t /*测试结果prodstyle prodsize prodcolor prodprice prodname
--------------------------------------------------------------
203   01,02,03 020,030 500 衣服 
204   01,02 030 600 裤子 (2 行受影响)
*/

Answer 4

看第四点./*
标题：普通行列转换(version 3.0)
作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，(version 2.0)增加sql server 2005的有关写法，(version 3.0)增加分数统计及项目合并等内容。
问题：假设有张学生成绩表(tb)如下:(相关字段说明：name-->姓名,course-->课程名称,type-->考试类型或次数,score-->成绩)
name course type score       
---- ------ ---- -----
李四 数学   1    81
李四 数学   2    82
李四 物理   1    83
李四 物理   2    84
李四 语文   1    85
李四 语文   2    86
张三 数学   1    91
张三 数学   2    92
张三 物理   1    93
张三 物理   2    94
张三 语文   1    95
张三 语文   2    96
*/
--创建测试数据
create table tb(name nvarchar(10),course nvarchar(10),type int,score int)
insert into tb values(N'李四',N'数学',1,81)
insert into tb values(N'李四',N'数学',2,82)
insert into tb values(N'李四',N'物理',1,83)
insert into tb values(N'李四',N'物理',2,84)
insert into tb values(N'李四',N'语文',1,85)
insert into tb values(N'李四',N'语文',2,86)
insert into tb values(N'张三',N'数学',1,91)
insert into tb values(N'张三',N'数学',2,92)
insert into tb values(N'张三',N'物理',1,93)
insert into tb values(N'张三',N'物理',2,94)
insert into tb values(N'张三',N'语文',1,95)
insert into tb values(N'张三',N'语文',2,96)
go
/*
1、需要如下结果(对每个人每门课程每次的成绩进行横向排列)
name course type_1 type_2      
---- ------ ------ ------
李四 数学   81     82
李四 物理   83     84
李四 语文   85     86
张三 数学   91     92
张三 物理   93     94
张三 语文   95     96
*/
--1.1 SQL SERVER 2000 静态SQL，指考试类型或次数type固定为1或2。(以下同)
select name,course, 
  max(case type when 1 then score else 0 end) type_1,
  max(case type when 2 then score else 0 end) type_2
from tb
group by name,course
order by name,course
--1.2 SQL SERVER 2000 动态SQL，指考试类型或次数type值不确定，不是固定为1或2。(以下同)
declare @sql varchar(8000)
set @sql = 'select name,course'
select @sql = @sql + ',max(case type when '''+ltrim(type)+''' then score else 0 end) [type_'+ltrim(type)+']'
from (select distinct type from tb) as a
set @sql = @sql + ' from tb group by name,course'
exec(@sql)
--1.3 SQL SERVER 2005 静态SQL。
select name,course,[1] type_1,[2] type_2 from (select * from tb) a pivot (max(score) for type in ([1],[2])) b order by name,course
--1.4 SQL SERVER 2005 动态SQL。
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
select @sql1=isnull(@sql1+'],[','')+ltrim(type) from tb group by type
set @sql1 = '['+@sql1+']'
select @sql2 = isnull(@sql2+'],[','')+ltrim(type)+'] [type_'+ltrim(type) from tb group by type
set @sql2 = 'select name,course,['+@sql2+']'
exec(@sql2 + ' from (select * from tb) a pivot (max(score) for type in ('+@sql1+')) b order by name,course')/*
2、需要如下结果(对每个人每门课程每次的成绩，总分，平均分，最高分，最低分进行横向排列)
name course type_1 type_2 sum avg   max min
---- ------ ------ ------ --- ----- --- ---
李四 数学   81     82     163 81.50 82  81
李四 物理   83     84     167 83.50 84  83
李四 语文   85     86     171 85.50 86  85
张三 数学   91     92     183 91.50 92  91
张三 物理   93     94     187 93.50 94  93
张三 语文   95     96     191 95.50 96  95
*/
--2.1 SQL SERVER 2000 静态SQL。
select name,course,
  max(case type when 1 then score else 0 end) type_1,
  max(case type when 2 then score else 0 end) type_2,
  sum(score) [sum],
  cast(avg(score*1.0) as decimal(18,2)) [avg],
  max(score) [max],
  min(score) [min]
from tb
group by name,course
order by name,course
--2.2 SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select name,course'
select @sql = @sql + ',max(case type when '''+ltrim(type)+''' then score else 0 end) [type_'+ltrim(type)+']'
from (select distinct type from tb) as a
set @sql = @sql + ',sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course'
exec(@sql) 
--2.3 SQL SERVER 2005 静态SQL。
select m.*,n.[sum],n.[avg],n.[max],n.[min] from
(select name,course,[1] type_1,[2] type_2 from (select * from tb) a pivot (max(score) for type in ([1],[2])) b) m,
(select name,course,sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course) n
where m.name=n.name and m.course=n.course
order by m.name,m.course
--2.4 SQL SERVER 2005 动态SQL。
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
select @sql1=isnull(@sql1+'],[','')+ltrim(type) from tb group by type
set @sql1='['+@sql1+']'
select @sql2=isnull(@sql2+'],[','')+ltrim(type)+'] [type_'+ltrim(type) from tb group by type
set @sql2='select m.*,n.[sum],n.[avg],n.[max],n.[min] from (select name,course,['+@sql2+']'
exec(@sql2+' from (select * from tb) a pivot (max(score) for type in ('+@sql1+')) b) m , 
(select name,course,sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course) n
where m.name=n.name and m.course=n.course 
order by name,course')/*
3、需要如下结果(对每个人每门课程的成绩进行统计再按照课程进行横向排列)
姓名 数学合计 物理合计 语文合计
---- -------- -------- --------
李四 163      167      171
张三 183      187      191
*/
--3.1 SQL SERVER 2000 静态SQL。
select name 姓名,
  sum(case course when '数学' then score else 0 end) [数学合计],
  sum(case course when '物理' then score else 0 end) [物理合计],
  sum(case course when '语文' then score else 0 end) [语文合计]
from tb
group by name 
order by name
--3.2 SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql='select name 姓名'
select @sql=@sql+',sum(case course when '''+course+''' then score else 0 end) ['+course+'合计]'
from (select distinct course from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
--3.3 SQL SERVER 2005 静态SQL。
select name [姓名],[数学] [数学合计],[物理] [物理合计],[语文] [语文合计] from (select name,course,score from tb) a pivot (sum(score) for course in ([数学],[物理],[语文])) b order by name
--3.4 SQL SERVER 2005 动态SQL。
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
select @sql1=isnull(@sql1+'],[','')+course from tb group by course
set @sql1='['+@sql1+']'
select @sql2=isnull(@sql2+'],[','')+course+'] ['+course+N'合计' from tb group by course
set @sql2 = 'select name '+N'[姓名'+'],['+@sql2+']'
exec(@sql2 + ' from (select name,course,score from tb) a pivot (sum(score) for course in ('+@sql1+')) b order by name')/*
4、需要如下结果(对每个人每门课程的成绩进行合并后再按照课程进行横向排列)
姓名 数学组合 物理组合 语文组合
---- -------- -------- --------
张三 91,92    93,94    95,96
李四 81,82    83,84    85,86
*/
--4.1 SQL SERVER 2000 静态SQL。需要使用函数先合并数据再进行行列转换。
--自定义函数实现字符串合并问题。
go
create function dbo.f_str(@name nvarchar(10),@course nvarchar(10)) returns varchar(100)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+cast(score as varchar) from tb where name=@name and course=@course
  set @str=right(@str,len(@str)-1)
  return @str
end
go
--实现行列转换
select name 姓名,
  max(case course when '数学' then score else '' end) [数学组合],
  max(case course when '物理' then score else '' end) [物理组合],
  max(case course when '语文' then score else '' end) [语文组合]
from
(
  --调用函数
  select name,course,score=dbo.f_str(name,course) from tb group by name,course
) t
group by name
order by name
drop function dbo.f_str
--4.2 SQL SERVER 2000 动态SQL。仍然需要使用上述字符串合并的函数。
declare @sql varchar(8000)
set @sql='select name 姓名'
select @sql=@sql+',max(case course when '''+course+''' then score else '''' end) ['+course+'组合]'
from (select distinct course from tb) as a
set @sql=@sql+' from (select name,course,score=dbo.f_str(name,course) from tb group by name,course) t group by name order by name'
exec(@sql)
--4.3 SQL SERVER 2005 静态SQL。
select name 姓名,
  [数学组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'数学' for xml path('')),1,1,''),
  [物理组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'物理' for xml path('')),1,1,''),
  [语文组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'语文' for xml path('')),1,1,'')
from tb t
group by name
--4.4 SQL SERVER 2005 动态SQL。
declare @sql nvarchar(4000)
set @sql = 'select name '+N'[姓名]'
select @sql = @sql + ',stuff((select '',''+ltrim(score) from tb where name=t.name and course=N'''+course+''' for xml path('''')),1,1,'''') ['+course+N'组合]'
from (select distinct course from tb) as a
set @sql = @sql+' from tb t group by name order by name'
exec(@sql)drop table tb/*
针对上面的行列转换进行逆向实现。表原始数据如下：
name type 数学 物理 语文          
---- ---- ---- ---- ----
李四 1    81   83   85
李四 2    82   84   86
张三 1    91   93   95
张三 2    92   94   96
*/--创建测试数据
create table tb(name nvarchar(10),type int,数学 int,物理 int,语文 int)
insert into tb values(N'李四',1,81,83,85)
insert into tb values(N'李四',2,82,84,86)
insert into tb values(N'张三',1,91,93,95)
insert into tb values(N'张三',2,92,94,96)
go/*
5.想要的结果如下：
name course type score       
---- ------ ---- -----
李四 数学   1    81
李四 数学   2    82
李四 物理   1    83
李四 物理   2    84
李四 语文   1    85
李四 语文   2    86
张三 数学   1    91
张三 数学   2    92
张三 物理   1    93
张三 物理   2    94
张三 语文   1    95
张三 语文   2    96
*/--5.1 SQL SERVER 2000 静态SQL
select name,course='数学',type,score=数学 from tb
union all
select name,course='物理',type,score=物理 from tb
union all
select name,course='语文',type,score=语文 from tb
order by name,course,type
--5.2 SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select name,[course]='+quotename(Name,'''')+',type,[score]='+quotename(Name)+' from tb'
from syscolumns 
where name!=N'name' and name!=N'type' and ID=object_id('tb')--表名tb，不包含列名为name和type的其它列
order by colid asc
exec(@sql+' order by name,course,type')
--5.3 SQL SERVER 2005 静态SQL。
select name,course,type,score from tb unpivot (score for course in([数学],[物理],[语文])) t order by name,course,type
--5.4 SQL SERVER 2005 动态SQL。
declare @sql nvarchar(4000)
select @sql=isnull(@sql+' union all ' , '')+' select name,[course]=N'+quotename(Name,'''')+',type,[score]='+quotename(Name)+' from tb'
from syscolumns
where name!=N'name' and name!=N'type' and ID=object_id('tb')
order by colid asc
exec(@sql+' order by name,course,type')