594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @sql VARCHAR(max)
SET @sql =
'select [FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE]'
SELECT @sql = @sql + ',max(case fname when ''' + fname +
''' then sum_price else null end)[' + fname + ']'
FROM (
SELECT DISTINCT fname
FROM #tab
) a
SET @sql = @sql +
' from #tab group by [FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE]'
PRINT @sql
EXEC (@sql)
[/quote]
可以了,谢谢,辛苦了
DECLARE @sql VARCHAR(max)
SET @sql =
'select [FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE]'
SELECT @sql = @sql + ',max(case fname when ''' + fname +
''' then sum_price else null end)[' + fname + ']'
FROM (
SELECT DISTINCT fname
FROM #tab
) a
SET @sql = @sql +
' from #tab group by [FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE]'
PRINT @sql
EXEC (@sql)
use Tempdb
go
--> --> 听雨停了-->测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([FOfferItem] int,[FUnit] int,[FQty] decimal(18,10),[FSupplier] int,[FNOTE] nvarchar(23),[fname] nvarchar(39),[sum_price] NVARCHAR(50))
Insert #tab
select 256,252,10.0000000000,264,N'147',N'李四','100.00000000000' union all
select 259,252,2000.0000000000,263,N'001',N'1800000.00000000000',null union all
select 0,0,0.0000000000,0,N'001',N'0.00000000000',null union all
select 259,252,2000.0000000000,264,N'002',N'0.00000000000',null union all
select 0,0,0.0000000000,0,N'002',N'0.00000000000',null union all
select 255,252,1000.0000000000,263,N'张三',N'1000000.00000000000',null union all
select 0,0,0.0000000000,0,N'张三',N'0.00000000000',null union all
select 255,252,20.0000000000,262,N'123',N'李四',200.00000000000
--测试数据结束
--方法1
SELECT *
FROM #tab PIVOT(MAX(sum_price) FOR fname IN ([001],[002],[李四],[张三]))a
--方法2
SELECT [FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE],
MAX(CASE fname WHEN '001' THEN sum_price ELSE null END) [001],
MAX(CASE fname WHEN '002' THEN sum_price ELSE null END) [002],
MAX(CASE fname WHEN '李四' THEN sum_price ELSE null END) [李四],
MAX(CASE fname WHEN '张三' THEN sum_price ELSE null END) [张三]
FROM #tab
GROUP BY
[FOfferItem],
[FUnit],
[FQty],
[FSupplier],
[FNOTE]
在我这边是可以出来的 ,第一种方法出不来可能是你sql版本的 问题,你用第二种试试看
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
[/quote]
执行后报错[/quote]
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
PRINT '插入临时表成功'
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
这样执行下,把打印的内容截图出来看看
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
[/quote]
执行后报错[/quote]
把print @name和print @sql的值贴出来看看
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
[/quote]
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
[/quote]
--把以下查询结果插入临时表#tab
SELECT a.FOfferItem,
a.FUnit,
a.FQty,
a.FSupplier,
a.FNOTE,
c.fname,
--FPrice * FQty这个前面最好都写上表的别名,也就是到底是a.FPrice还是b.FPrice
ISNULL(CONVERT(VARCHAR(50), FPrice * FQty),'') AS sum_price
INTO #tab
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b
ON a.fid = b.FID
INNER JOIN t_emp c
ON c.fitemid = b.FOfficeClerk
--实现行转列显示
DECLARE @name VARCHAR(MAX),
@sql VARCHAR(MAX)
SET @name = STUFF(
(
SELECT DISTINCT ',[' + fname + ']'
FROM #tab FOR XML PATH('')
),
1,
1,
''
)
PRINT @name
SET @sql = 'SELECT * from #tab pivot(max(sum_price)for fname in(' + @name + '))a'
PRINT @sql
EXEC (@sql)
declare @sql varchar(max)
select @sql=ISNULL(@sql+',','')+'case when c.fname='''+fname+''' then convert(varchar(50),FPrice*FQty) else '''' end as '+fname+''
from (select distinct fname from #t_emp) as A
set @sql='SELECT a.FOfferItem,a.FUnit,a.FQty,a.FSupplier,a.FNOTE,'+@sql+'
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b ON a.fid = b.FID
INNER JOIN t_emp c ON c.fitemid = b.FOfficeClerk'
exec(@sql)