表中的列转行。行数为字段数量

kevin87923 2010-09-29 10:20:54

create table device
(
D_ID int IDENTITY(1,1) NOT NULL,
D_Model nvarchar(50), --分类
D_Temp2 nvarchar(50), --型号或规格
D_Temp3 nvarchar(50), --封装
D_Temp4 nvarchar(50), --外形尺寸
D_Temp5 nvarchar(50), --脚距
D_Temp6 nvarchar(50) --脚直径
)
go
insert into device values(N'电阻','1','2','3','4','5')
insert into device values(N'电阻','11','22','33','44','55')
insert into device values(N'电容','12','23','34','45','56')
insert into device values(N'电阻2','13','24','35','46','57')
insert into device values(N'电阻1','22','32','43','54','65')
insert into device values(N'电阻','15','26','30','44','56')
insert into device values(N'电阻3','17','28','63','84','55')
insert into device values(N'电阻4','15','42','53','74','56')
………………………………………………………………………………





想要的效果

分类: 电阻 电阻1 电阻2 ……
型号或规格: 11 15 22 ……
封装: 22 26 32 ……
外形尺寸: 3 …………………………
脚距: ………………………………
脚直径: ………………………………

网上例子是多,可我还是不懂。
...全文
104 点赞 收藏 15
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
starseeker7 2010-09-30
看這樣行不



declare @cols Nvarchar(2000)
declare @cols1 Nvarchar(2000)
declare @cols2 Nvarchar(2000)
declare @cols3 Nvarchar(2000)
declare @cols4 Nvarchar(2000)
set @cols=''
set @cols1=''
set @cols2=''
set @cols3=''
set @cols4=''

select @cols=@cols+N',sum((case when D_Model=N'''+CAST(D_Model AS NVARCHAR(10))+N''' then CAST(D_Temp2 AS INT) else 0 end )) as ['+CAST(D_Model AS NVARCHAR(10))+'] '
,@cols1=@cols1+N',sum((case when D_Model=N'''+CAST(D_Model AS NVARCHAR(10))+N''' then CAST(D_Temp3 AS INT) else 0 end )) as ['+CAST(D_Model AS NVARCHAR(10))+'] '
,@cols2=@cols2+N',sum((case when D_Model=N'''+CAST(D_Model AS NVARCHAR(10))+N''' then CAST(D_Temp4 AS INT) else 0 end )) as ['+CAST(D_Model AS NVARCHAR(10))+'] '
, @cols3=@cols3+N',sum((case when D_Model=N'''+CAST(D_Model AS NVARCHAR(10))+N''' then CAST(D_Temp5 AS INT) else 0 end )) as ['+CAST(D_Model AS NVARCHAR(10))+'] '
, @cols4=@cols4+N',sum((case when D_Model=N'''+CAST(D_Model AS NVARCHAR(10))+N''' then CAST(D_Temp6 AS INT) else 0 end )) as ['+CAST(D_Model AS NVARCHAR(10))+'] '
from (SELECT DISTINCT D_Model FROM device GROUP BY D_Model) AS T

EXEC( N'select ''[型号或规格]'' '+@cols+N' from device union all '+
N'select ''[封装]'' '+@cols1+N' from device union all '+
N'select ''[外形尺寸]'' '+@cols2+N' from device union all '+
N'select ''[脚距]'' '+@cols3+N' from device union all '+
N'select ''[脚直径]'' '+@cols4+N' from device '
)

回复
kevin87923 2010-09-29
[Quote=引用 9 楼 forfumm 的回复:]

貌似楼主要这样的结果吧,
分类 电容, 电阻, 电阻1, 电阻2
型号或规格: 12 1、11、15 ..........
封装: 23 2、22、26 .........
外形尺寸: 34 3、33、30 ........
脚距: 45 4、44 ........……
[/Quote]
对啊, 就是这种效果
回复
ForFumm 2010-09-29
貌似楼主要这样的结果吧,
分类 电容, 电阻, 电阻1, 电阻2
型号或规格: 12 1、11、15 ..........
封装: 23 2、22、26 .........
外形尺寸: 34 3、33、30 ........
脚距: 45 4、44 .........
脚直径: 56 5、55、56 .....
回复
dawugui 2010-09-29
貌似提供的信息不准确,帮顶.
回复
kevin87923 2010-09-29
[Quote=引用 5 楼 pxxxp 的回复:]
去重复?
那结果中
电阻
11
22
3
是原表中哪行?
[/Quote]
先每列的值去除重复的。 在将每列数据转换成行
回复
changjiangzhibin 2010-09-29
看不懂耶
回复
PxxxP 2010-09-29
[Quote=引用 3 楼 kevin87923 的回复:]

显示的结果, 是去了重复的。
[/Quote]

去重复?
那结果中
电阻
11
22
3
是原表中哪行?
回复
SQLCenter 2010-09-29
另外,起这样的字段名,你还不如a,b,c,d... 至少帮你码代码的人可以轻松点
回复
kevin87923 2010-09-29
显示的结果, 是去了重复的。
回复
SQLCenter 2010-09-29
我也看不懂要什么效果
回复
PxxxP 2010-09-29
结果不对啊?
回复
kingbox2076 2010-09-29
鄙人用的是SQL2005自带函数PIVOT直接行转列。
但LZ提及“动态列转行”的问题,鄙人认为:
1、DISTINCT出D_Model值
2、编写函数,对上述结果拼接
3、编写SQL语句,执行EXCE(sql)
回复
kevin87923 2010-09-29
谢谢各位的关注, 不过还不是我要的效果,
网上的资料确实有许多,不过还是弄不懂这动态列转行。
希望有高手给代码看下, 看看这动态列转行的执行过程
分类: 电阻 电阻 电阻1 电阻2 ……
型号或规格: 11 1 22 13 ……
封装: 22 2 32 24 ……
外形尺寸: 33 3 43 35 ……
脚距: 44 4 54 46 ……
脚直径: 55 5 65 57 ……
回复
喜-喜 2010-09-29
貌似动态行列转换!不写了..自己查些资料
回复
ForFumm 2010-09-29

还差最后一步,只做到一行5个属性。最后自己弄弄


create table device
(
D_ID int IDENTITY(1,1) NOT NULL,
D_Model nvarchar(50), --分类
D_Temp2 nvarchar(50), --型号或规格
D_Temp3 nvarchar(50), --封装
D_Temp4 nvarchar(50), --外形尺寸
D_Temp5 nvarchar(50), --脚距
D_Temp6 nvarchar(50) --脚直径
)
go
insert into device values(N'电阻','1','2','3','4','5')
insert into device values(N'电阻','11','22','33','44','55')
insert into device values(N'电容','12','23','34','45','56')
insert into device values(N'电阻2','13','24','35','46','57')
insert into device values(N'电阻1','22','32','43','54','65')
insert into device values(N'电阻','15','26','30','44','56')
insert into device values(N'电阻3','17','28','63','84','55')
insert into device values(N'电阻4','15','42','53','74','56')




if object_id('tb') is not null
drop table tb
select id=identity(int,1,1),d_model into tb from device group by d_model



CREATE FUNCTION DBO.F_STR2(@LB VARCHAR(50),@ID INT ) --STR2
RETURNS VARCHAR(8000)
AS
BEGIN
declare @str2 varchar(1000)
set @str2 = ''
select @str2 = @str2 + ',' + cast(a.d_temp2 as varchar) from
( select a.d_model,id,a.d_temp2 from (select a.*,b.id from device a left join tb b on a.d_model=b.d_model) a group by a.d_model,id,a.d_temp2 )a
where A.d_model = @LB and id=@id
set @str2 = right(@str2 , len(@str2)-1)
RETURN @STR2
END
GO

CREATE FUNCTION DBO.F_STR3(@LB VARCHAR(50),@ID INT ) --STR3
RETURNS VARCHAR(8000)
AS
BEGIN
declare @str3 varchar(1000)
set @str3 = ''
select @str3 = @str3 + ',' + cast(a.d_temp3 as varchar) from
( select a.d_model,id,a.d_temp3 from (select a.*,b.id from device a left join tb b on a.d_model=b.d_model) a group by a.d_model,id,a.d_temp3 )a
where A.d_model = @LB and id=@id
set @str3 = right(@str3 , len(@str3)-1)
RETURN @STR3
END
GO

CREATE FUNCTION DBO.F_STR4(@LB VARCHAR(50),@ID INT ) --STR4
RETURNS VARCHAR(8000)
AS
BEGIN
declare @str4 varchar(1000)
set @str4 = ''
select @str4 = @str4 + ',' + cast(a.d_temp4 as varchar) from
( select a.d_model,id,a.d_temp4 from (select a.*,b.id from device a left join tb b on a.d_model=b.d_model) a group by a.d_model,id,a.d_temp4 )a
where A.d_model = @LB and id=@id
set @str4 = right(@str4 , len(@str4)-1)
RETURN @STR4
END
GO

CREATE FUNCTION DBO.F_STR5(@LB VARCHAR(50),@ID INT ) --STR5
RETURNS VARCHAR(8000)
AS
BEGIN
declare @str5 varchar(1000)
set @str5 = ''
select @str5 = @str5 + ',' + cast(a.d_temp5 as varchar) from
( select a.d_model,id,a.d_temp5 from (select a.*,b.id from device a left join tb b on a.d_model=b.d_model) a group by a.d_model,id,a.d_temp5 )a
where A.d_model = @LB and id=@id
set @str5 = right(@str5 , len(@str5)-1)
RETURN @STR5
END
GO


CREATE FUNCTION DBO.F_STR6(@LB VARCHAR(50),@ID INT ) --STR6
RETURNS VARCHAR(8000)
AS
BEGIN

declare @str6 varchar(1000)
set @str6 = ''
select @str6 = @str6 + ',' + cast(a.d_temp6 as varchar) from
( select a.d_model,id,a.d_temp6 from (select a.*,b.id from device a left join tb b on a.d_model=b.d_model) a group by a.d_model,id,a.d_temp6 )a
where A.d_model = @LB and id=@id

set @str6 = right(@str6 , len(@str6)-1)
RETURN @STR6
END
GO

SELECT d_model,m2=dbo.F_STR2(d_model,id),m3=dbo.F_STR3(d_model,id),m4=dbo.F_STR4(d_model,id), m5=dbo.F_STR5(d_model,id),
m6=dbo.F_STR6(d_model,id)

FROM TB order by id


电容 12 23 34 45 56
电阻 1,11,15 2,22,26 3,30,33 4,44 5,55,56
电阻1 22 32 43 54 65
电阻2 13 24 35 46 57
电阻3 17 28 63 84 55
电阻4 15 42 53 74 56
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-29 10:20
社区公告
暂无公告