表的数据如下:(行不定)
a b c d
1 小李 1 2
2 小王 4 5
1 小李 3 4
2 小王 6 7
1 小李 5 6
2 小王 8 9
.......我需要通过行转列如下
l 小李 1 2 3 4 5 6 .....
2 小王 4 5 6 7 8 9 ......
先谢谢先
a b c d
1 小李 1 2
2 小王 4 5
1 小李 3 4
2 小王 6 7
1 小李 5 6
2 小王 8 9
.......我需要通过行转列如下
l 小李 1 2 3 4 5 6 .....
2 小王 4 5 6 7 8 9 ......
先谢谢先
解决方案 »
- 就网站应用而言,将数据表的某些字段设为不可为空值有什么具体意义?
- 初来咋到 大家帮帮忙 在线等
- SQL2000, 如何在存储过程中调用外部webservice,返回一个xml结构的dataset,然后将数据写入到一个数据表中? ###100分跪求##
- sqlserver数据库中如何用一字符替换某一字段中的内容非null的某个字符。。
- oracle初学着,请问与sqlserver有什么不同
- 怎样得到类似这样的一个结果集?
- MSSQL2005联表查询问题
- MSSQL如果表中数据大于2000万条,那要用什么方法查
- 高手们,帮看看这种语句应该怎么写!!!
- 这个错误是什么意思呀
- SQLSERVER2005表中数据超过100W条怎么考虑性能问题?
- 那里有Transact sql的入门资料
--测试数据
create table csdn(id int,txt varchar(10))
insert csdn
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc' union all
select 3,'aaa' union all
select 3,'bbb'
select * from csdn
gocreate function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,'')
end
goselect id,dbo.Gettxt(id) txt from csdn group by id
godrop function Gettxt
drop table csdn
go
有表rowtocol,其数据如下:
a b
1 1
1 2
1 3
2 1
2 2
3 1
如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 创建一个合并的函数 create function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果:
select distinct a ,dbo.f_rowtocol(a) from rowtocol
set @str=''
select @str=@str +',' +txt from csdn where id=@id
return stuff(@str,1,1,'')
http://topic.csdn.net/t/20031108/13/2440306.html
insert roy
select 1, '小李', 1, 2 union all
select 2, '小王', 4, 5 union all
select 1, '小李', 3, 4 union all
select 2, '小王', 6, 7 union all
select 1, '小李', 5, 6 union all
select 2, '小王', 8, 9
begin TRANSACTION
select * ,d=1 into roy1
from (select a,b,c from roy
union all
select a,b,d from roy)a
declare @a int
set @a=0
update roy1
set d=@a,@a=@a+1
select a,b,c,d=(select count(*)from roy1 where a=a.a and d!>a.d )into roy2
from roy1 a
declare @s varchar(1000)
set @s=''
select @s=@s+',['+convert(varchar,d)+']=sum(case d when '''+convert(varchar,d)+'''then c else '''' end)'
from roy2 group by d
set @s='select b'+@s+ 'from roy2 group by b'
exec (@s)
ROLLBACK TRANSACTION
结果如下:(所影响的行数为 12 行)
(所影响的行数为 12 行)
(所影响的行数为 12 行)b 1 2 3 4 5 6
---------- ----------- ----------- ----------- ----------- ----------- -----------
小李 1 3 5 2 4 6
小王 4 6 8 5 7 9
假设有张学生成绩表(tb_rowtocol)如下
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94declare @sql varchar(4000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from rowtocol) as a
set @sql = @sql + ' from rowtocol group by name'
exec(@sql)
如果上述两表互相换一下:即
表名(cj)
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94想变成 Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94select 姓名 as Name,'语文' as Subject,语文 as Result from CJ union
select 姓名 as Name,'数学' as Subject,数学 as Result from CJ union
select 姓名 as Name,'物理' as Subject,物理 as Result from CJ
order by 姓名 desc
insert roy
select 1, '小李', 1, 2 union all
select 2, '小王', 4, 5 union all
select 1, '小李', 3, 4 union all
select 2, '小王', 6, 7 union all
select 1, '小李', 5, 6 union all
select 2, '小王', 8, 9
begin TRANSACTION
select * ,d=1 into roy1
from (select a,b,c from roy
union all
select a,b,d from roy)a
declare @a int
set @a=0
update roy1
set d=@a,@a=@a+1
select a,b,c,d=(select count(*)from roy1 where a=a.a and d!>a.d )into roy2
from roy1 a
declare @s varchar(1000)
set @s=''
select @s=@s+',['+convert(varchar,d)+']=sum(case d when '''+convert(varchar,d)+'''then c else '''' end)'
from roy2 group by d
set @s='select a,b'+@s+ 'from roy2 group by a,b'
exec (@s)
ROLLBACK TRANSACTION
如下:(所影响的行数为 12 行)
(所影响的行数为 12 行)
(所影响的行数为 12 行)a b 1 2 3 4 5 6
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 小李 1 3 5 2 4 6
2 小王 4 6 8 5 7 9
insert roy
select 1, '小李', 1, 2 union all
select 2, '小王', 4, 5 union all
select 1, '小李', 3, 4 union all
select 2, '小王', 6, 7 union all
select 1, '小李', 5, 6 union all
select 2, '小王', 8, 9begin TRANSACTION
select * ,d=1 into roy1
from (select a,b,c from roy
union all
select a,b,d from roy)a order by b,c asc--排序方式
declare @a int
set @a=0
update roy1
set d=@a,@a=@a+1
select a,b,c,d=(select count(*)from roy1 where a=a.a and d!>a.d )into roy2
from roy1 a
declare @s varchar(1000)
set @s=''
select @s=@s+',['+convert(varchar,d)+']=sum(case d when '''+convert(varchar,d)+'''then c else '''' end)'
from roy2 group by d
set @s='select a,b'+@s+ 'from roy2 group by a,b'
exec (@s)
ROLLBACK TRANSACTION楼主要的效果改一下排序方式就行了(所影响的行数为 12 行)
(所影响的行数为 12 行)
(所影响的行数为 12 行)a b 1 2 3 4 5 6
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 小李 1 2 3 4 5 6
2 小王 4 5 6 7 8 9
begin TRANSACTION和ROLLBACK TRANSACTION之间的
所有语句都不会对数据库产生影响
insert roy
select 1, '小李', 1, 2 union all
select 2, '小王', 4, 5 union all
select 1, '小李', 3, 4 union all
select 2, '小王', 6, 7 union all
select 1, '小李', 5, 6 union all
select 2, '小王', 8, 9begin TRANSACTION
select * ,d=1 into roy1
from (select a,b,c from roy
union all
select a,b,d from roy)a order by b,c asc--排序方式
declare @a int
set @a=0
update roy1
set d=@a,@a=@a+1
select a,b,c,d=(select count(*)from roy1 where a=a.a and d!>a.d )into roy2
from roy1 a
declare @s varchar(1000)
set @s=''
select @s=@s+',['+convert(varchar,d)+']=sum(case d when '''+convert(varchar,d)+'''then c else '''' end)'
from roy2 group by d
set @s='select a,b'+@s+ 'from roy2 group by a,b'
exec (@s)
ROLLBACK TRANSACTION楼主要的效果改一下排序方式就行了(所影响的行数为 12 行)
(所影响的行数为 12 行)
(所影响的行数为 12 行)a b 1 2 3 4 5 6
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
1 小李 1 2 3 4 5 6
2 小王 4 5 6 7 8 9
阁下还要努力才可以成为高手啊,不要误会,我说的是抄袭.
楼主那一段看不懂
1.包含两个表------典型行列转换问题例子
--建立测试环境
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
go--删除测试环境
Drop Table tb1,tb2
------------------------------------------------------------------
select
模号 = a.MoldId,
工件 = a.WorkpieceId,
铜公 = a.CopperId,
爆公数量 = a.BurstCopper,
粗公数量 = a.RoughCopper,
幼公数量 = a.ChildrenCopper,
CNC = max(case b.ProcessType when 'CNC' then State end),
车床 = max(case b.ProcessType when '车床' then State end),
铣床 = max(case b.ProcessType when '铣床' then State end),
线切割 = max(case b.ProcessType when '线切割' then State end),
雕刻 = max(case b.ProcessType when '雕刻' then State end),
执模 = max(case b.ProcessType when '执模' then State end),
EDM = max(case b.ProcessType when 'EDM' then State end)
from
Coppers a
inner join
Processes b
on
a.MoldId = b.MoldId
group by
a.MoldId,a.WorkpieceId,a.CopperId,
a.BurstCopper,a.RoughCopper,a.ChildrenCopper
2、动态SQL语句(适合"加工类型"类不固定的情况):
------------------------------------------------------------------
declare @s varchar(8000)
set @s = ''
select
@s = @s + ','+ProcessType
+ '=max(case b.ProcessType when '''+ ProcessType+''' then State end)'
from
Processes
group by
ProcessType
set @s = 'select 模号 = a.MoldId,'
+' 工件 = a.WorkpieceId,'
+' 铜公 = a.CopperId,'
+' 爆公数量 = a.BurstCopper,'
+' 粗公数量 = a.RoughCopper,'
+' 幼公数量 = a.ChildrenCopper'
+ @s
+' from Coppers a inner join Processes b on a.MoldId = b.MoldId'
+' group by a.MoldId,a.WorkpieceId,a.CopperId'
+' ,a.BurstCopper,a.RoughCopper,a.ChildrenCopper'
exec(@s)