SQL Server-将行合并为一列并占用最大数量

Ask_SO 发表于 Dev

Ask_SO

我正在研究一个查询，该查询将从多个表中收集数据并显示数据。

数据如下所示：

Region                  CountryName  ProductID
Latin America/Caribbean BAHAMAS         21
Europe                  AZERBAIJAN      23
Europe                  AZERBAIJAN      24
Latin America/Caribbean BAHAMAS         21
Middle East/Africa      BAHRAIN         43
Europe                  BALI            21
Asia Pacific            BANGLADHESH     25
Asia Pacific            BANGLADHESH     256
Latin America/Caribbean BARBADOS        20      
Europe                  BELARUS         15
Europe                  BELGIUM         24

我想要这样的结果集

Region                  CountryName                         ProductID       RecordCount
Latin America/Caribbean BAHAMAS,BARBADOS                    21,20           2
Europe                  AZERBAIJAN,BALI,BELARUS,BELGIUM     23,15,24        4
Asia Pacific            BANGLADHESH                         25,26           2
Middle East/Africa      BAHRAIN                             43              1

我已经在下面的查询中尝试合并CountryName和ProductID，但是我想要第4列计数值，该值一直是最大的黑白Product＆Country列。（即，如果CountryName列具有4个国家，Product列具有2，则我的计数应为4（上述结果集中的第二行），如果Product列具有两个ID并且国家/地区具有单个国家/地区名称，则我的计数应为2（第三行）在上述结果中），并且应按地区分组。

SELECT RegionName,
STUFF((SELECT DISTINCT ', ' + CAST(COUNTRY AS NVARCHAR(20)) FROM Countries RX WHERE RX.CountryCode = PD.CountryCode FOR XML PATH('')),1,1,'') AS CountryList,
STUFF((SELECT DISTINCT ', ' + CAST(ProductID AS NVARCHAR(5)) FROM Product RP WHERE RP.ProductID = PD.ProductID FOR XML PATH('')),1,1,'') AS ProductList,
Count(?) AS recCount
FROM Countries CN WITH (NOLOCK)
INNER JOIN Product PD WITH (NOLOCK)
ON PD.CountryCode=CN.CountryCode 
GROUP BY RegionName

有人对如何做到这一点有想法吗？

苏拉杰·库马尔

您可以使用stuff()，len()函数和内部联接尝试以下查询。

create table #Region (Region  Varchar(50), CountryName Varchar(50),  ProductID int)
insert into #Region values
('Latin America/Caribbean', 'BAHAMAS', 21),
('Europe', 'AZERBAIJAN', 23),
('Europe', 'AZERBAIJAN', 24),
('Latin America/Caribbean', 'BAHAMAS', 21),
('Middle East/Africa', 'BAHRAIN', 43),
('Europe', 'BALI', 21),
('Asia Pacific', 'BANGLADHESH', 25),
('Asia Pacific', 'BANGLADHESH', 256),
('Latin America/Caribbean', 'BARBADOS', 20),    
('Europe', 'BELARUS', 15),
('Europe', 'BELGIUM', 24)

现在，实际查询如下所示：

Select a.Region, a.CountryName, b.ProductID, 
Case when LEN(b.ProductID) - LEN(REPLACE(b.ProductID, ',',''))+1 
> 
LEN(a.CountryName) - LEN(REPLACE(a.CountryName, ',',''))+1
then 
    LEN(b.ProductID) - LEN(REPLACE(b.ProductID, ',',''))+1
else
    LEN(a.CountryName) - LEN(REPLACE(a.CountryName, ',',''))+1
end as RecordCount
 from(
Select
    g1.Region
    , stuff((
        select distinct ', ' + g.CountryName
        from #Region g        
        where g.Region = g1.Region 
        for xml path('')
    ),1,2,'') as CountryName    
from #Region g1
group by g1.Region
)a inner join
(
Select
    g1.Region
    , stuff((
        select distinct ', ' + Cast(g.ProductID as CHAR(3))
        from #Region g        
        where g.Region = g1.Region 
        for xml path('')
    ),1,2,'') as ProductID    
from #Region g1
group by g1.Region
)b on a.Region = b.Region

输出如下所示。

Region                  CountryName                      ProductID     RecordCount
-----------------------------------------------------------------------------------
Asia Pacific              BANGLADHESH                        25 , 256             2
Europe                    AZERBAIJAN, BALI, BELARUS, BELGIUM 15 , 21 , 23 , 24    4
Latin America/Caribbean   BAHAMAS, BARBADOS                  20 , 21              2
Middle East/Africa        BAHRAIN                            43                   1

您可以在此处找到现场演示。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。