access如何将列的数据转为行的数据，如图

redyasu

我对原数据进行了查询，结果如 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
但我想要一行来显示，如：

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
还要如下显示：

请问如何实现上述两个效果？请大侠不吝赤脚。

redyasu

看了个帖子，但还是看不懂，姚明啊
有表A,
ProductId,SubjectId
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
ProductId,SubjectId
  1 1,2,3
  2 1,2
  3 1

创建一个合并的函数  

create function fmerg(@ProductId int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str='
select @str=@str+','+cast(SubjectId as varchar) from 表A where ProductId=@ProductId
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果

select distinct ProductId,dbo.fmerg(ProductId) from 表A

redyasu

又找了个，谁能翻译一下？就是思路过程及函数讲解
SqlServer 行列转换

博客分类： SqlServer
SQLGo.netF#
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)

想变成

姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78


declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)

drop table test



eg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!


--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A  

2:
/*********** 行转列 *****************/
测试：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)

declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')

a
-----------
15
9
1
0
1
2
2
0  

3:
将结果矩阵转置。
http://expert.csdn.net/Expert/topic/2390/2390314.xml?temp=.4681055
老衲：


if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P')
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
end

go
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('表的行数太多了，我转不了',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end

redyasu

redyasu 发表于 2012-2-14 11:47
看了个帖子，但还是看不懂，姚明啊
有表A,
ProductId,SubjectId

看不懂str是什么意思，为什么加@？

redyasu

高效SQL——合并多个字段值或多条记录
作者：李书琴出处：论坛2007-12-10 11:30
为什么说是高效呢?因为摒弃了游标和函数的途径，而采用变量的方式来保存值。也就是说避免了游标和函数自身的缺点。
　　高效SQL——从无主键表中合并字段值
以下是引用片段：
　　create table #T1
　　(
　　A varchar(10),
　　B varchar(20)
　　)
　　insert into #T1 values ('aa','1')
　　insert into #T1 values ('aa','9a')
　　insert into #T1 values ('bb','1')
　　insert into #T1 values ('bb','10')
　　insert into #T1 values ('bb','16')
　　insert into #T1 values ('aa','16')
　　insert into #T1 values ('aa','17')
　　insert into #T1 values ('aa','30')
　　insert into #T1 values ('bb','6df')
　　insert into #T1 values ('aa','5')
　　insert into #T1 values ('aa','8')
　　insert into #T1 values ('aa','ed')
　　所要的结果：
　　aa 1,9a,16,17,30,5,8,ed
　　bb 1,10,16,6df
　　解决方法：
以下是引用片段：
　　declare @c varchar(1024)
　　set @c=''
　　declare @x char(10),@y char(10)
　　set @x=''
　　set @y=''
　　select @y=@x,@x=x.a,@c=@c + (case @x when @y then ',' else ';' + x.a + ':' end)
　　+x.d from (select b as d,a from #t1) as x order by x.a
　　set @c=substring(@c,2,len(@c)-1)
　　select @c
　　为什么说是高效呢?
　　因为摒弃了游标和函数的途径，而采用变量的方式来保存值
　　也就是说避免了游标和函数自身的缺点
　　如果是简单地把字段值合并的sql语句：
　　select c1||c2||c3 as name from table
　　中间可以添加字符常量和数字，如
　　select c1||'test'||c2||c3 as name from table
　　select c1||2||c2||c3 as name from table
　　如何用一条SQL语句，将多条记录(一个字段)合并为一个?
　　例如：
　　table字段为：tableID(nchar)
　　查询结果为不确定的多条：
　　tableID
　　T1
　　T2
　　T3
　　T4
　　……
　　如何用一条SQL语句将这些记录合并为一个字段，值为：'T1T2T3……'
以下是引用片段：
　　create table t
　　(tableid nchar(30))
　　insert t
　　select 'T1' union all
　　select 'T2' union all
　　select 'T3' union all
　　select 'T4' union all
　　select 'T5' union all
　　select 'T6'
　　go
　　create function f_he()
　　returns @t table(col varchar(50))
　　as
　　begin
　　declare @sql varchar(50)
　　set @sql=''
　　select @sql=@sql+ltrim(rtrim(tableid)) from t
　　insert @t values (@sql)
　　return
　　end
　　go
　　select * from t
　　select * from dbo.f_he()
　　drop function f_he
　　drop table t
　　col
　　--------------------------------------------------
　　T1T2T3T4T5T6
　　(所影响的行数为 1 行)

roych

这些都是SQL Sever里的存储过程，对Access无效。
由于这种问题太多了，所以我不会再回答的。如果你有Access中国的账号，可参考以下帖子：
http://www.access-cn.com/thread-96091-1-1.html
如果没有，建议申请一个：
http://www.access-cn.com/?fromuser=roych

redyasu

roych 发表于 2012-2-14 20:33
这些都是SQL Sever里的存储过程，对Access无效。
由于这种问题太多了，所以我不会再回答的。如果你有Acces ...

谢谢你！发自内心的！

szjohnchan

貌似access的交叉表就可以解决了吧，何必那么复杂。