如何使用sql查询查看带有group by子句的逗号分隔字符串中的列值？

nszi6y05 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(365)

如何在下面的sql查询（在sql server 2014中）中获取带有逗号分隔字符串的u\u producttype列。它需要按docnum分组，并且在转换为逗号分隔的字符串时不需要u\u producttype中的空值。我还尝试了“for xml path”选项。这是我的问题供你参考，

SELECT
    ORDR.DocNum, ORDR.CardCode, ORDR.CardName, ORDR.SlpCode,
    OSLP.SlpName, OITM.U_Product_Type,
    SUM (RDR1.LineTotal) LineTotal,
    ISNULL (SUM (DPI1.LineTotal), 0) dwpnt
FROM
    ORDR
INNER JOIN 
    RDR1 ON RDR1.DocEntry = ORDR.DocEntry   
LEFT JOIN 
    OITM ON OITM.ItemCode = RDR1.ItemCode
LEFT JOIN 
    OSLP ON OSLP.SlpCode = ORDR.SlpCode
LEFT JOIN 
    DPI1 ON DPI1.BaseEntry = RDR1.DocEntry 
         AND DPI1.TargetType <> 14 
         AND DPI1.BaseLine = RDR1.LineNum
LEFT JOIN 
    RCT2 ON RCT2.DocEntry = DPI1.DocEntry
LEFT JOIN  
    ORCT ON RCT2.DocNum = ORCT.DocEntry AND ORCT.Canceled = 'N'
WHERE 
    ORDR.CANCELED = 'N' 
    AND ISNULL (DPI1.LineTotal, 0) = 0 
    AND ORDR.SlpCode = 67
GROUP BY 
    ORDR.DocNum, ORDR.CardCode, ORDR.CardName, ORDR.SlpCode,
    OSLP.SlpName, OITM.U_Product_Type

结果和我的要求如下，

sql sql-server group-by comma

来源：https://stackoverflow.com/questions/62965211/how-to-view-a-column-values-in-comma-separated-string-with-group-by-clause-using

1条答案

按热度按时间

pprl5pva1#

你可以用 STUFF() 如下所示。只要替换一下 cte 你的原始查询。另一种方法是用当前结果创建临时表并替换它 cte 使用新创建的临时表。

SELECT DoNum
        , CardCode
        , CardName
        , SipCode
        , SlpName
        , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,U_Product_Type) 
         FROM [cte] t1
         WHERE (DoNum = cte.DoNum
         and CardCode = t1.CardCode
         and CardName = t1.CardName
         and SipCode = t1.SipCode
         and SlpName = t1.SlpName) 
         FOR XML PATH ('')), 1, 2, '') AS U_Product_Type
         ,sum(LineTotal) as LineTotal
         ,sum(dwpnt) as dwpnt
FROM cte
group by DoNum
, CardCode
, CardName
, SipCode
, SlpName

现场演示

展开查看全部

赞(0）回复(0）举报 2021-07-29

我来回答

如何使用sql查询查看带有group by子句的逗号分隔字符串中的列值？

1条答案

相关问题

热门标签

最新问答