I am working on a query that will collect data from joining multiple tables and display the data.
The data looks like this:
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
And I want the result set like this
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
I have tried below Query to merge CountryName and ProductID but I want the 4th Column count value which ever is maximum b/w Product&Country columns. (i.e., If CountryName column has 4 Countries and Product column has 2 then my count should be 4 (2nd Row in the above resultset) and If Product column has two Id and country has single country name then my Count should be 2(3rd row in the above resulset) and it should be grouped by Region.
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
Does anyone have an idea on how to make this?
You can try the following query using stuff(), len() functions and inner join.
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)
Now the actual query is as shown below:
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
The output is as shown below.
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
You can find the live demo Here.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加