question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked YitzhakKhabinsky-0887 edited

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

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-generalsql-server-transact-sql
image.png (3.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @ahmedsalah-1628,

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        |
 +-----------+----------+----------------+--------------------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ahmedsalah-1628 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can you help me by modified xquery script i put above

0 Votes 0 ·