22,301
社区成员




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')
………………………………………………………………………………
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 '
)
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