i can't group data by company and year and revision for countriesvalues id ?

ahmed salah 3,216 Reputation points
2022-05-24T19:36:29.74+00:00

I work on sql server 2017 i face issue i can't group data by company and year and revision
then order data pipe by countriesvalue asc

so i need to get every countries related to every company and year and revision separated pipe and order by id country value asc
from smalled to biggest

so how to modify query below to get expected result

when run query below

    create table #FinalCountrisData  
    (  
    CompanyId int,  
    StrtYear int,  
    StrtRevisionId int,  
    countriesValues int  
    )  
    insert into #FinalCountrisData(CompanyId,StrtYear,StrtRevisionId,countriesValues)  
    values  
    (121,2010,12331,888),  
    (121,2010,12331,999),  
    (121,2010,12331,666),  

    (121,2011,12331,111),  
    (121,2011,12331,333),  
    (121,2011,12331,555),  

    (121,2012,12331,222),  
    (121,2012,12331,444),  
    (121,2012,12331,777)  

    ;with t as (  

      select   
          row_number() over(partition by   st.CompanyId,   
          st.StrtYear,   
          st.StrtRevisionId order by st.CompanyId asc) as rn,  
          st.CompanyId,   
          st.StrtYear,   
          st.StrtRevisionId,  
          st.countriesValues  
      from #FinalCountrisData st  
    )  
     select   
      t1.CompanyId,  
      t1.StrtYear,  
      t1.StrtRevisionId,  
    (  
      SELECT countriesValues AS [r] FROM t  
      FOR XML PATH(''), TYPE, ROOT('root')  
     ).query('  
     for $x in distinct-values(/root/r/text())  
     let $token := data(concat("[",$x,"]"))  
     return   
     if ($x eq (distinct-values(/root/r/text())[last()])[1]) then $token  
           else concat($token, ",")  
     ').value('.', 'NVARCHAR(MAX)') AS CountriesDataStuff  

    from t as t1  
    where t1.rn = 1  
    order by t1.CompanyId  


wrong result  

CompanyId   StrtYear    StrtRevisionId  CountriesDataStuff  
121 2010    12331   [888], [999], [666], [111], [333], [555], [222], [444], [777]  
121 2011    12331   [888], [999], [666], [111], [333], [555], [222], [444], [777]  
121 2012    12331   [888], [999], [666], [111], [333], [555], [222], [444], [777]  

expected result

CompanyId StrtYear StrtRevisionId CountriesDataStuff  
121 2010 12331 666|888|999  
121 2011 12331 111|333|555  
121 2012 12331 222|444|777

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,485 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,831 Reputation points
    2022-05-25T02:22:24.327+00:00

    Hi @ahmed salah ,

    Please try the following solution.
    It seems there is no need to use XQuery FLWOR expression.

    SQL

    -- DDL and sample data population, start  
    DECLARE @FinalCountrisData table   
    (  
     CompanyId int,  
     StrtYear int,  
     StrtRevisionId int,  
     countriesValues int  
    );  
    INSERT INTO @FinalCountrisData (CompanyId,StrtYear,StrtRevisionId,countriesValues) VALUES  
    (121,2010,12331,888),  
    (121,2010,12331,999),  
    (121,2010,12331,666),  
    (121,2011,12331,111),  
    (121,2011,12331,333),  
    (121,2011,12331,555),  
    (121,2012,12331,222),  
    (121,2012,12331,444),  
    (121,2012,12331,777);  
    -- DDL and sample data population, end  
      
    SELECT CompanyId,  
    	StrtYear,  
    	StrtRevisionId,  
    	STUFF((  
    		SELECT '|' + TRY_CAST(countriesValues AS VARCHAR(10)) AS [text()]   
    		FROM @FinalCountrisData AS c  
    		WHERE c.CompanyId = p.CompanyId  
    			AND c.StrtYear = p.StrtYear  
    			AND c.StrtRevisionId = p.StrtRevisionId  
    		ORDER BY countriesValues  
    		FOR XML PATH('')  
    	),1,1,'') AS CountriesDataStuff  
    FROM @FinalCountrisData AS p  
    GROUP BY CompanyId, StrtYear, StrtRevisionId;  
    

    Output

    +-----------+----------+----------------+--------------------+  
    | CompanyId | StrtYear | StrtRevisionId | CountriesDataStuff |  
    +-----------+----------+----------------+--------------------+  
    |       121 |     2010 |          12331 | 666|888|999        |  
    |       121 |     2011 |          12331 | 111|333|555        |  
    |       121 |     2012 |          12331 | 222|444|777        |  
    +-----------+----------+----------------+--------------------+  
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 110.8K Reputation points
    2022-05-24T19:43:32.51+00:00

    Try a query:

    select CompanyId, StrtYear, StrtRevisionId, 
        string_agg(countriesValues, '|') within group (order by countriesValues) as CountriesDataStuff
    from #FinalCountrisData
    group by CompanyId, StrtYear, StrtRevisionId
    

    It assumes that the duplicates are not possible.