急急,行转列的问题,来者有分!

[mysohu]

表的数据如下:(行不定)
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 ......
先谢谢先

[mysohu]

谢谢,还有没有高手,写出来更简单的
等待中....

[allright_flash]

declare @s varchar(8000)
set @str=''
select @str=@str +',' +txt from csdn where id=@id
return stuff(@str,1,1,'')

[marco08]

jf

[xyxfly]

谢谢,还有没有高手,写出来更简单的


呵呵，其实都差不多了

[SupermanZgn]

学习

[tuwicn]

TO: zhouyan024(SPRINGPRINCE)

复制别人的也要一点技巧啊,你复制同一个贴别人的答案的勇气,让我大开眼界了
阁下还要努力才可以成为高手啊,不要误会,我说的是抄袭.

[zhouyan024]

create table roy(a int,b varchar(10),c int,d int)
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 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

[xiantao123]

我也来学习学习

[奇玉]

SQL Server2005中有现成的行列转置函数。

[starrygis]

学习学习

[junmail]

路过，学习！

[abc_sk]

,,,

[中国风]

To:mysohu (21世纪最重要的就是我!：）)
楼主那一段看不懂

[marco08]

學習+接分

[yuedeem]

接分

[real_name]

:)

[Well]

/*引用*/
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

[zbluestar]

1、静态SQL语句(适合"加工类型"类固定的情况)：
------------------------------------------------------------------
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)

[早起晚睡]

学习了

[DavidNoWay]

jf