行转列

[buzhunmeiyouni]

怎么把两条数据显示在一行，就是物料名称相同的数据显示在同一行



ALTER PROC [dbo].[p_lgcs]
--@fbillno VARCHAR(20)
AS
SET NOCOUNT on
BEGIN
SELECT
CASE h.fnumber
WHEN '0' THEN isnull(CONVERT(VARCHAR(100),a.FPrice * a.FQty*(a.fprice*1.32)),'')
WHEN 'p' THEN isnull(CONVERT(VARCHAR(100),a.FPrice * a.FQty*(a.fprice*1.17)),'')
WHEN 'z3' THEN isnull(CONVERT(VARCHAR(100),a.FPrice * a.FQty*(a.fprice*1.14)),'')
WHEN 'z6' THEN isnull(CONVERT(VARCHAR(100),a.FPrice * a.FQty*(a.fprice*1.11)),'')
WHEN 'z17' THEN ISNULL(CONVERT(VARCHAR(50), a.FPrice * a.FQty),'') end AS sum_price,
d.FName AS 物料名称 ,d.FModel AS 规格型号,f.FName AS 单位 ,a.FQty AS 数量,a.ftsupplier,a.FNOTE AS 备注,
a.FDliveryTime AS 货期,DATEDIFF(d,b.FDate,j.FReceived_Date) AS 时间差,e.fnumber AS 付款方式,c.fname AS nam,g.zz,i.FBillNo

INTO ##cs
FROM LGEntry_OfferSheet a --报价单表体
INNER JOIN LGBOS_OfferSheet b ON a.fid = b.FID --报价单表头
INNER JOIN dbo.LGBOS_InquirySheet i ON i.FBillNo=a.FBillNo_SRC--询价单表头
INNER JOIN dbo.LGEntry_InquirySheet j ON a.FID_SRC=j.FID AND a.FEntryID_SRC=j.FEntryID --询价单表体
INNER JOIN t_item c ON c.fitemid = b.FOfficeClerk
INNER JOIN t_icitem d ON d.FItemID=a.FOfferItem
--INNER JOIN t_item e ON e.FItemID=a.FSupplier
INNER JOIN t_item f ON f.fitemid=a.FUnit
INNER JOIN dbo.LG_BOSRate h ON h.fid=a.FCess
INNER JOIN dbo.LG_BOSPayment e ON e.fid=a.FPayment
INNER JOIN (SELECT a.FEntryID,
+'单价：'+(convert(nvarchar(20),convert(decimal(18,2),(fprice)))+', '
+'货期：'
+ CAST(a.FDliveryTime AS varchar(20))+'天 , '
+'付款方式：'+ e.fnumber+', '
+' 税率：' +f.fnumber +' , '
+' 备注：'+CHAR(10)+ a.FNOTE) AS zz
FROM LGEntry_OfferSheet a
INNER JOIN dbo.LG_BOSPayment e ON e.fid=a.FPayment
INNER JOIN dbo.LG_BOSPayment f ON f.fid=a.FCess )g ON g.fentryid=a.FEntryID
--WHERE i.fbillno=@fbillno

DECLARE @sql VARCHAR(max)
SET @sql =
'select [物料名称],
[规格型号],
[单位],
[数量],
[备注],
[货期],
[时间差],
[付款方式]'
SELECT @sql = @sql + ',max(case nam when ''' + nam + ''' then sum_price else '''' end)[' + nam + ']'
+ ',max(case nam when ''' + nam + ''' then ftsupplier else '''' end)[' + nam +' 供应商]'
+ ',max(case nam when ''' + nam +''' then zz else '''' end)[' + nam +' 中标情况]'
FROM ( SELECT DISTINCT nam
FROM ##cs)a
SET @sql=@sql +'into cs from ##cs group by
[物料名称],
[规格型号],
[单位],
[数量],
[备注],
[货期],
[时间差],
[付款方式]
'
PRINT @sql
EXEC (@sql)
END

[吉普赛的歌]

太多了不想看了， 希望达到类似效果：

DECLARE @t TABLE ( [物料名称] NVARCHAR(20),  otherCol nvarchar(20))
INSERT INTO @t VALUES ('教师耳机','a')
INSERT INTO @t VALUES ('教师耳机','b')
SELECT a.[物料名称],stuff(
(
	SELECT ','+otherCol FROM @t AS b WHERE a.[物料名称]=b.[物料名称] for xml path('')
)
,1,1,''
) AS othercols
FROM @t  AS a
GROUP BY a.[物料名称]
/*
物料名称    othercols
教师耳机	a,b
*/

[吉普赛的歌]

最好是贴出 表结构， 测试数据 的demo脚本 以及最终的结果

[buzhunmeiyouni]