22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#Main') is null
drop table #Main
Go
Create table #Main([FID] int,[FNAME] nvarchar(24),[HOUDU] decimal(18,7),[MENDIAN] nvarchar(22))
Insert #Main
select 1,N'H123',1.2,N'AB' union all
select 2,N'H278',1.2,N'IJ'
GO
if not object_id(N'Tempdb..#Detail') is null
drop table #Detail
Go
Create table #Detail([FID] int,[FTYPE] nvarchar(22),[ALIAS] nvarchar(24),[FPRICE] int)
Insert #Detail
select 1,N'箱包',N'K186',24 union all
select 1,N'鞋材',N'H123',27 union all
select 2,N'箱包',N'H278',32 union all
select 2,N'测试类型',N'H278',10032
Go
--测试数据结束
DECLARE @sql1 VARCHAR(8000)
SET @sql1 = 'select a.FID ,a.FNAME ,a.HOUDU'
SELECT @sql1 = @sql1 + ' , max(case FTYPE when ''' + RTRIM(b.FTYPE)
+ ''' then ALIAS else null end) [' + RTRIM(FTYPE)+ ']'
FROM (SELECT DISTINCT FTYPE FROM #Detail) b
SELECT @sql1 = @sql1 + ' , max(case FTYPE when ''' + RTRIM(b.FTYPE)
+ ''' then FPRICE else null end) [' + RTRIM(FTYPE)+'单价' + ']'
FROM (SELECT DISTINCT FTYPE FROM #Detail) b
SET @sql1 = @sql1 + ' from #Main a
JOIN #Detail b ON b.FID = a.FID
GROUP BY a.FID ,
a.FNAME ,
a.HOUDU'
EXEC( @sql1 )
if not object_id(N'Tempdb..#Tmp_M') is null
drop table #Tmp_M
Go
create table #Tmp_M
(
FID int,
FNAME varchar(10),
HOUDU decimal(13,2),
MENDIAN varchar(10)
)
insert into #Tmp_M
select 1,'H123',1.2,'AB' union
Select 2,'H278',1.2,'IJ'
if not object_id(N'Tempdb..#Tmp_D') is null
drop table #Tmp_D
Go
create table #Tmp_D
(
FID int,
FTYPE nvarchar(10),
AIALS varchar(10),
FPRICE int
)
insert into #Tmp_D
select 1,N'箱包','K186',24 union
select 1,N'鞋材','K123',27 union
select 2,N'箱包','K278',32
Alter Table #Tmp_M Add 箱包 varchar(10)
Alter Table #Tmp_M Add 鞋材 varchar(10)
Alter Table #Tmp_M Add 箱包单价 int
Alter Table #Tmp_M Add 鞋材单价 int
UPDATE #Tmp_M
SET 箱包=AIALS,箱包单价=FPrice
From #Tmp_M a
Join #Tmp_D b on a.Fid=b.Fid
Where FTYPE=N'箱包'
UPDATE #Tmp_M
SET 鞋材=AIALS,鞋材单价=Fprice
From #Tmp_M a
Join #Tmp_D b on a.Fid=b.Fid
Where FTYPE=N'鞋材'
--测试数据
if not object_id(N'Tempdb..#Main') is null
drop table #Main
Go
Create table #Main([FID] int,[FNAME] nvarchar(24),[HOUDU] decimal(18,7),[MENDIAN] nvarchar(22))
Insert #Main
select 1,N'H123',1.2,N'AB' union all
select 2,N'H278',1.2,N'IJ'
GO
if not object_id(N'Tempdb..#Detail') is null
drop table #Detail
Go
Create table #Detail([FID] int,[FTYPE] nvarchar(22),[ALIAS] nvarchar(24),[FPRICE] int)
Insert #Detail
select 1,N'箱包',N'K186',24 union all
select 1,N'鞋材',N'H123',27 union all
select 2,N'箱包',N'H278',32
Go
--测试数据结束
SELECT a.FID ,
a.FNAME ,
a.HOUDU ,
MAX(CASE WHEN b.FTYPE = '箱包' THEN ALIAS
ELSE NULL
END) AS 箱包 ,
MAX(CASE WHEN b.FTYPE = '鞋材' THEN ALIAS
ELSE NULL
END) AS 鞋材 ,
MAX(CASE WHEN b.FTYPE = '箱包' THEN FPRICE
ELSE NULL
END) AS 箱包单价,
MAX(CASE WHEN b.FTYPE = '鞋材' THEN FPRICE
ELSE NULL
END) AS 鞋材单价
FROM #Main a
JOIN #Detail b ON b.FID = a.FID
GROUP BY a.FID ,
a.FNAME ,
a.HOUDU