如何高效的实现行转列！
现有一个数据表table1（Time datetime,Address varchar(100),ProductName varchar(100),SaleCnt int）,该表的典型数据如下：
Time Address ProductName SaleCnt
2003-10-3 北京 product1 5
2003-10-3 北京 product2 2
2003-10-3 北京 product3 7
2003-10-3 北京 product5 1
2003-10-3 南京 product2 3
2003-10-3 南京 product5 7
2003-10-4 北京 product5 6
其中，ProductName只有6种不同的名字，table1中的记录有50万条，现在想将该表的信息按以下格式显示出来（还是以上面的典型数据为例）
Time Address product1 product2 product3 product4 product5 product6
2003-10-3 北京 5 2 7 0 1 0
2003-10-3 南京 0 3 0 0 7 0
2003-10-4 北京 0 0 0 0 0 6
问题点数：100、回复次数：11Top
1 楼mywhsw（bamboo）回复于 2003-12-03 11:05:53 得分 0
--建议建立中间表B_COL_DISTRIBUTE--如果不需要就把它删除掉
--参考以下sql
--建立表
DECLARE CUR_A CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' and name not in('dtproperties','B_COL_DISTRIBUTE')
DECLARE @TABLENAME VARCHAR(50)
DECLARE @TMPSTR VARCHAR(8000)
declare @TMPSTR2 Varchar(8000)
open CUR_A
FETCH NEXT FROM CUR_A INTO @TABLENAME
SET @TMPSTR=''
SET @TMPSTR2=''
WHILE @@FETCH_STATUS = 0 BEGIN
SET @TMPSTR=@TMPSTR+@TABLENAME +' VARCHAR(2) NULL,'+CHAR(13)
SET @TMPSTR2=@TMPSTR2+
'CONSTRAINT [DF_B_COL_DISTRIBUTE_'+@TABLENAME+'] DEFAULT ('''') FOR ['+@TABLENAME+'],'
+CHAR(13)
FETCH NEXT FROM CUR_A INTO @TABLENAME
END
SET @TMPSTR=('
create table B_COL_DISTRIBUTE
(
COL_NAME VARCHAR(50) NULL,
COL_MEMO VARCHAR(50) NULL,
'+SUBSTRING(@TMPSTR,1,(LEN(@TMPSTR)-2))+')'+char(13)+
'ALTER TABLE B_COL_DISTRIBUTE WITH NOCHECK ADD
CONSTRAINT [DF_B_COL_DISTRIBUTE_COL_NAME] DEFAULT ('''') FOR [COL_NAME],
CONSTRAINT [DF_B_COL_DISTRIBUTE_COL_MEMO] DEFAULT ('''') FOR [COL_MEMO],'+char(13)+
+SUBSTRING(@TMPSTR2,1,(LEN(@TMPSTR2)-2))+ char(13) )
EXEC ('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[B_COL_DISTRIBUTE]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [dbo].[B_COL_DISTRIBUTE]
')
EXECUTE ( @TMPSTR)
--PRINT @TMPSTR
CLOSE CUR_A
DEALLOCATE CUR_A
go
---插入记录
DECLARE CUR_B CURSOR FOR
SELECT DISTINCT
t2.name AS COLNAME, CONVERT(varchar, t4.[value]) AS COLMEMO
FROM dbo.sysproperties t4 RIGHT OUTER JOIN
dbo.systypes t3 RIGHT OUTER JOIN
dbo.syscolumns t2 ON t3.xtype = t2.xtype RIGHT OUTER JOIN
dbo.sysobjects t1 ON t2.id = t1.id ON t4.id = + t1.id AND
t4.smallid = + t2.colid
WHERE (t1.xtype = 'U' and t1.name not in('dtproperties','B_COL_DISTRIBUTE'))
DECLARE @COL_NAME VARCHAR(1000)
DECLARE @COL_MEMO VARCHAR(1000)
DECLARE @TBL_NAME VARCHAR(1000)
open CUR_B
FETCH NEXT FROM CUR_B INTO @COL_NAME,@COL_MEMO
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO B_COL_DISTRIBUTE (COL_NAME,COL_MEMO) VALUES (@COL_NAME,isnull(@COL_MEMO,''))
DECLARE CUR_C CURSOR FOR
SELECT t1.name
FROM dbo.sysobjects t1 INNER JOIN
dbo.syscolumns t2 ON t1.id = t2.id INNER JOIN
dbo.systypes t3 ON t2.xtype = t3.xtype LEFT OUTER JOIN
dbo.sysproperties t4 ON t1.id = t4.id AND t2.colid = t4.smallid
WHERE (t1.xtype = 'U') and (t2.name=@COL_NAME ) and t1.name not in('dtproperties','B_COL_DISTRIBUTE')
open CUR_C
FETCH NEXT FROM CUR_C INTO @TBL_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
exec('UPDATE B_COL_DISTRIBUTE SET '+@TBL_NAME+'=''√'' WHERE COL_NAME='''+@COL_NAME+'''')
FETCH NEXT FROM CUR_C INTO @TBL_NAME
END
CLOSE CUR_C
DEALLOCATE CUR_C
FETCH NEXT FROM CUR_B INTO @COL_NAME,@COL_MEMO
END
CLOSE CUR_B
DEALLOCATE CUR_B
Top
2 楼CrazyFor（冬眠的鼹鼠）回复于 2003-12-03 11:15:36 得分 0
参考:
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
select @sql = left(@sql,len(@sql)-1) + ' from #'
exec(@sql)
drop table #Top
3 楼welyngj（无爱）回复于 2003-12-03 11:16:32 得分 0
select convert(varchar(10),address,120) time,address,max(case ProductName when
'product1' then SaleCnt end) product1,
max(case ProductName when
'product2' then SaleCnt end) product2,
max(case ProductName when
'product3' then SaleCnt end) product3,
max(case ProductName when
'product4' then SaleCnt end) product4,
max(case ProductName when
'product5' then SaleCnt end) product5,max(case ProductName when
'product6' then SaleCnt end) product6
from table1Top
4 楼welyngj（无爱）回复于 2003-12-03 11:21:37 得分 30
sorry!
select convert(varchar(10),time,120) time,address,max(case ProductName when
'product1' then SaleCnt end) product1,
max(case ProductName when
'product2' then SaleCnt end) product2,
max(case ProductName when
'product3' then SaleCnt end) product3,
max(case ProductName when
'product4' then SaleCnt end) product4,
max(case ProductName when
'product5' then SaleCnt end) product5,max(case ProductName when
'product6' then SaleCnt end) product6
from table1
Top
5 楼qyyok（）回复于 2003-12-03 17:11:02 得分 0
select time,sum(case productname when 'Product1' then salecnt
end) product1,sum(case productname when 'product2' then salecnt end) as product2,sum(case productname when 'Product3' then salecnt
end) product3,sum(case productname when 'Product4' then salecnt
end) product4,sum(case productname when 'Product5' then salecnt
end) product5,sum(case productname when 'Product6' then salecnt
end) product6 from tabel1 group by time order by timeTop
6 楼pengdali（）回复于 2003-12-03 17:32:38 得分 30
declare @sql varchar(8000)
set @sql = 'select [Time],Address'
select @sql = @sql + ',sum(case ProductName when '''+ProductName+''' then SaleCnt else 0 end) ['+ProductName+']'
from (select distinct ProductName from table1) as a
select @sql = @sql+' from table1 group by [Time],Address'
exec(@sql)
goTop
7 楼kenpa（大海无量）回复于 2003-12-04 01:52:19 得分 0
行是不确定的!!上面可以做吗?
数据库版有大力这样的人在真是太好了...Top
8 楼pengdali（）回复于 2003-12-04 08:41:50 得分 0
可以Top
9 楼zjcxc（邹建）回复于 2003-12-04 08:44:34 得分 0
--ProductName值不确定,当然是用动态SQL语句. 为方便调用,可以用存储过程:
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s = ''
select @sql = @s + ',['+ProductName
+']=sum(case ProductName when '''
+ProductName+''' then SaleCnt else 0 end)'
from (select distinct ProductName from table1) a
exec('select [Time],Address'+@s+' from table1 group by [Time],Address')
set nocount off
go
Top
10 楼zjcxc（邹建）回复于 2003-12-04 08:47:58 得分 40
--为了格式化显示日期,可以做如下调整.
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+ProductName
+']=sum(case ProductName when '''
+ProductName+''' then SaleCnt else 0 end)'
from (select distinct ProductName from table1) a
exec('select [time]=convert(varchar(10),[Time],120),Address'+@s+' from table1 group by [Time],Address')
set nocount off
go
Top
11 楼zjcxc（邹建）回复于 2003-12-04 08:51:37 得分 0
--如果你的ProductName 值很多,用下面的方法可以避免处理出错.
create proc p_qry1
as
declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic varchar(20)
--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a= ',['+ProductName
+']=sum(case ProductName when '''
+ProductName+''' then SaleCnt else 0 end)'
into # from(select distinct ProductName from table1) a
--判断需要多少个变量来处理
select @i=max(len(a)) from #
set @i=3800/@i
--分组临时表
update # set gid=id/@i
select @i=max(gid) from #
--生成数据处理语句
select @sqlhead='''select [time]=convert(varchar(10),[Time],120),Address'''
,@sqlend=''' from table1 group by [Time],Address'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' nvarchar(4000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,4000)
--执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)
--删除临时表
drop table #
go
Top
