请看这个行列转换，可以吗？

Question

数据库存储方式如下：shop       prodoct      sum      datesshop1        pro1       10       20090911
shop1        pro2       5        20090911
shop1        pro3       5        20090911
shop2        pro1       10       20090911
shop2        pro2       5        20090911
shop3        pro1       5        20090911
......我想查询转换成如下格式：    
shop     pro1    pro2    pro3
shop1    10        5       5
shop2    10        5       0
shop3    5         0       0
......
谢谢

Answer 1

注：shop1到shopN的pro并不是都有，比如说shop1可能pro1到proN都有，shop2可能只能pro1，在显示时pro没有的就显示0

Answer 2

注（2）：还有这个条件：where like '%20090911%'

Answer 3

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]GO
动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78(8 行受影响)
*/

Answer 4

create  table  tempTable(
shop varchar(20),
      prodoct varchar(20),
[sum] int,
 dates  datetime
)
insert into tempTable select 'shop1',        'pro1',      10,      '20090911'
insert into tempTable select 'shop1',        'pro2',      5,        '20090911'
insert into tempTable select 'shop1',        'pro3',      5,        '20090911'
insert into tempTable select 'shop2',        'pro1',      10,      '20090911'
insert into tempTable select 'shop2',        'pro2',      5 ,       '20090911'
insert into tempTable select 'shop3',        'pro1',      5,        '20090911'select * from  tempTable
declare @sql varchar(5000)
select @sql=isnull(@sql+',','')+'isnull(max(case when prodoct='''+prodoct+''' then [sum] end),0) as ['+prodoct+']'
from tempTable group by prodoctexec('select shop,'+@sql+' from tempTable group by shop')

Answer 5

shop pro1 pro2 pro3
shop1 10 5 5
shop2 10 5 0
shop3 5 0 0

Answer 6

select 
    shop,isnull(pro1,0),isnull(pro2,0), isnull(pro3,0)
from 
    TableName
pivot 
    (max([sum]) for [product] in([pro1],[pro2],[pro3]))b 

Answer 7

create  table  tempTable(
shop varchar(20),
      prodoct varchar(20),
[sum] int,
 dates  varchar(20)
)
insert into tempTable select 'shop1',        'pro1',      10,      '20090911'
insert into tempTable select 'shop1',        'pro2',      5,        '20090911'
insert into tempTable select 'shop1',        'pro3',      5,        '20090911'
insert into tempTable select 'shop2',        'pro1',      10,      '20090911'
insert into tempTable select 'shop2',        'pro2',      5 ,       '20090911'
insert into tempTable select 'shop3',        'pro1',      5,        '20090911'
insert into tempTable select 'shop3',        'pro2',      5,        '20090912'select * from  tempTable/*shop prodoct sum dates
shop1 pro1 10 20090911
shop1 pro2 5 20090911
shop1 pro3 5 20090911
shop2 pro1 10 20090911
shop2 pro2 5 20090911
shop3 pro1 5 20090911
shop3 pro2 5 20090912
*/create proc up_GetTempTableByDate
@date varchar(20)=''
as
declare @sql varchar(5000)
select @sql=isnull(@sql+',','')+'isnull(max(case when prodoct='''+prodoct+''' then [sum] end),0) as ['+prodoct+']'
from tempTable  group by prodoctexec('select shop,'+@sql+' from tempTable  where dates like ''%'+@date+'%''  group by shop')exec up_GetTempTableByDate
/*
shop pro1 pro2 pro3
shop1 10 5 5
shop2 10 5 0
shop3 5 5 0
*/
exec up_GetTempTableByDate '20090911'
/*
shop pro1 pro2 pro3
shop1 10 5 5
shop2 10 5 0
shop3 5 0 0
*/

Answer 8

把代码写在存储过程里，就可以在.net 中调用了

Answer 9

--> 测试数据：[TB]
--> 我的淘宝：http://shop36766744.taobao.com/ if object_id('[TB]') is not null drop table [TB]
create table [TB]([shop] varchar(5),[prodoct] varchar(4),[sum] int,[dates] datetime)
insert [TB]
select 'shop1','pro1',10,'20090911' union all
select 'shop1','pro2',5,'20090911' union all
select 'shop1','pro3',5,'20090911' union all
select 'shop2','pro1',10,'20090911' union all
select 'shop2','pro2',5,'20090911' union all
select 'shop3','pro1',5,'20090911'
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([prodoct])+'=max(case when [prodoct]='+quotename([prodoct],'''')+' then [sum] else 0 end)'
from TB group by [prodoct]
exec('select [shop]'+@s+' from TB group by [shop]')
/*
shop  pro1        pro2        pro3
----- ----------- ----------- -----------
shop1 10          5           5
shop2 10          5           0
shop3 5           0           0(3 行受影响)*/drop table [TB]

Answer 10

select 
    shop
,isnull(pro1,0)as 'pro1'
,isnull(pro2,0)as 'pro2'
, isnull(pro3,0)as 'pro3'
from 
    (select * from C where date  like '%20090911%' )a
pivot 
    (max([sum]) for [product] in([pro1],[pro2],[pro3]))b

Answer 11

谢谢帮忙，那where dates like '%20090911%' 这个条件放在哪个位置呢？

Answer 12

declare @tempTable table
(
shop varchar(20),
prodoct varchar(20),
[sum] int,
 dates  datetime
)
insert into @tempTable select 'shop1','pro1',10,'20090911'
insert into @tempTable select 'shop1','pro2',5,'20090911'
insert into @tempTable select 'shop1','pro3',5,'20090911'
insert into @tempTable select 'shop2','pro1',10,'20090911'
insert into @tempTable select 'shop2','pro2',5,'20090911'
insert into @tempTable select 'shop3','pro1',5,'20090911'
select shop,
sum(case when prodoct='pro1' then [sum] else 0 end)'pro1',
sum(case when prodoct='pro2' then [sum] else 0 end)'pro2',
sum(case when prodoct='pro3' then [sum] else 0 end)'pro3'
from @tempTable group by shop
shop                 pro1        pro2        pro3
-------------------- ----------- ----------- -----------
shop1                10          5           5
shop2                10          5           0
shop3                5           0           0