34,576
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#tempA', 'u') is not null drop table #tempA;
go
create table #tempA( [供应商ID] INT, [供应商名称] varchar(100));
insert #tempA
select '1','供应商1' union all
select '2','供应商2' union all
select '3','供应商3'
if OBJECT_ID('tempdb..#tempB', 'u') is not null drop table #tempB;
go
create table #tempB( [供应商ID] INT, [产品名称] varchar(100), [报价] float);
insert #tempB
select 1, 'A','20' union all
select 1, 'B','21' union all
select 1, 'C','22' union all
select 2, 'A','19' union all
select 2, 'B','20' union all
select 2, 'C','21' union all
select 3, 'A','19.5' union all
select 3, 'B','22' union all
select 1, 'A','18.5' union all
select 1, 'B','20'
--SQL
DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX)
SET @colList = STUFF((SELECT DISTINCT ','+QUOTENAME(a.[供应商名称]) FROM #tempA a INNER JOIN #tempB b ON a.供应商ID=b.供应商ID FOR XML PATH('')),1,1,'')
SET @sql = N'
select * from
(
SELECT a.供应商名称, a.产品名称, b.报价 FROM
(SELECT DISTINCT a.供应商ID, a.供应商名称, [产品名称] FROM #tempA a inner join #tempB b on a.供应商ID=b.供应商ID) A
OUTER APPLY
(
SELECT TOP(1) * FROM
(SELECT rowid=ROW_NUMBER() OVER(PARTITION BY 供应商ID ORDER BY GETDATE()),* FROM #tempB) m
WHERE m.供应商ID=a.供应商ID AND m.[产品名称]=a.[产品名称] ORDER BY rowid DESC
) B
) a
pivot
(max(报价) for 供应商名称 in('+ @colList +')) b
'
EXEC(@sql)
/*
产品名称 供应商1 供应商2 供应商3
A 18.5 19 19.5
B 20 20 22
C 22 21 NULL
*/
create table 报价主表
(单号 int, 供应商 varchar(10))
create table 报价从表
(单号 int, 产品名称 varchar(10), 价格 decimal(5,1))
insert into 报价主表
select 1,'供应商1' union all
select 2,'供应商2' union all
select 3,'供应商3' union all
select 4,'供应商1'
insert into 报价从表
select 1, 'A', 20 union all
select 1, 'B', 21 union all
select 1, 'C', 22 union all
select 2, 'A', 19 union all
select 2, 'B', 20 union all
select 2, 'C', 21 union all
select 3, 'A', 19.5 union all
select 3, 'B', 22 union all
select 4, 'A', 18.5 union all
select 4, 'B', 20
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+供应商+']'
from (select distinct 供应商 from 报价主表) t
select @tsql='
with t as
(select a.产品名称,a.价格,b.供应商,
row_number() over(partition by a.产品名称,b.供应商 order by a.单号 desc) ''rn''
from 报价从表 a
inner join 报价主表 b on a.单号=b.单号
)
select 产品名称,'+@tsql+'
from (select 产品名称,供应商,价格 from t where rn=1) a
pivot(max(价格) for 供应商 in('+@tsql+')) b'
exec(@tsql)
/*
产品名称 供应商1 供应商2 供应商3
---------- ------------- ------------- -------------
A 18.5 19.0 19.5
B 20.0 20.0 22.0
C 22.0 21.0 NULL
(3 row(s) affected)
*/
CREATE TABLE #temp0 (id INT IDENTITY(1,1),vendor_no CHAR(10),good_no CHAR(10),price DECIMAL(10,2))
INSERT #temp0 (vendor_no,GOOD_NO,price)
SELECT 'VENDOR1','A',20 UNION ALL
SELECT 'VENDOR1','B',21 UNION ALL
SELECT 'VENDOR1','C',22 UNION ALL
SELECT 'VENDOR2','A',19 UNION ALL
SELECT 'VENDOR2','B',20 UNION ALL
SELECT 'VENDOR2','C',21 UNION ALL
SELECT 'VENDOR3','A',19.5 UNION ALL
SELECT 'VENDOR3','B',22 UNION ALL
SELECT 'VENDOR1','A',18.5 UNION ALL
SELECT 'VENDOR1','B',20
SELECT * INTO #temp FROM #temp0 a
WHERE id IN (SELECT MAX(id) FROM #temp0 WHERE vendor_no=a.vendor_no AND good_no=a.good_no)
ORDER BY id
DECLARE @sql VARCHAR(MAX), @colList VARCHAR(MAX)
SET @colList = STUFF(
(
SELECT ','+QUOTENAME(RTRIM(vendor_no))
FROM (SELECT DISTINCT vendor_no FROM #temp) t
FOR XML PATH('')
),1,1,'')
SET @sql = '
select * from (select vendor_no,good_no,price from #temp) a
pivot
(max(price) for vendor_no in('+ @colList +')) b'
EXEC(@sql)
create table #tb (供应商 nvarchar(10),产品名称 nvarchar(10),报价 decimal(10,1))
insert into #tb values('供应商1','A',20)
insert into #tb values('供应商1','B',21)
insert into #tb values('供应商1','C',22)
insert into #tb values('供应商2','A',19)
insert into #tb values('供应商2','B',20)
insert into #tb values('供应商2','C',21)
insert into #tb values('供应商3','A',19.5)
insert into #tb values('供应商3','B',22)
insert into #tb values('供应商1','A',18.5)
insert into #tb values('供应商1','B',20)
--动态sql
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(产品名称)+']=max(case rtrim(产品名称) when '''+rtrim(产品名称)+''' then 报价 end)'
from #tb group by rtrim(产品名称)
exec('select 供应商'+@sql+' from #tb group by 供应商')
drop table #tb
/*
供应商 A B C
供应商1 20.0 21.0 22.0
供应商2 19.0 20.0 21.0
供应商3 19.5 22.0 NULL
*/
create table tableA (供应商 nvarchar(10),产品名称 nvarchar(10),报价 decimal(10,1))
insert into tableA values('供应商1','A',20)
insert into tableA values('供应商1','B',21)
insert into tableA values('供应商1','C',22)
insert into tableA values('供应商2','A',19)
insert into tableA values('供应商2','B',20)
insert into tableA values('供应商2','C',21)
insert into tableA values('供应商3','A',19.5)
insert into tableA values('供应商3','B',22)
insert into tableA values('供应商1','A',18.5)
insert into tableA values('供应商1','B',20)
select a.产品名称,
供应商1=(select MIN(报价) from tableA where 供应商='供应商1' and 产品名称=a.产品名称),
供应商2=(select MIN(报价) from tableA where 供应商='供应商2' and 产品名称=a.产品名称),
供应商3=(select MIN(报价) from tableA where 供应商='供应商3' and 产品名称=a.产品名称)
from tableA a
group by a.产品名称
/*
A 18.5 19.0 19.5
B 20.0 20.0 22.0
C 22.0 21.0 NULL
*/