Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself

String Concat over partition in SQL?

A correlated subquery might be the simplest solution:

with l as (
      select l.*,
             cast(row_number() over (partition by user_id order by date_time) as signed) as seqnum
      from log_table l
     )
select l.*,
       (select group_concat(l2.event_name order by l2.date_time separator '')
        from l l2
        where l2.user_id = l.user_id and
              l2.seqnum between l.seqnum - 2 and l.seqnum + 2
       ) as new_event_name
from l;

If the event names are one character, you can eliminate the correlated subquery and use string operations:

with l as (
      select l.*, full_concat,
             cast(row_number() over (partition by user_id order by date_time) as signed) as seqnum
      from log_table l join
           (select user_id,  group_concat(event_name order by date_time separator '') as full_concat
            from log_table l
            group by user_id
           ) ll
           using (user_id)
     )
select l.*, substr(full_concat, greatest(seqnum - 2, 1), least(5, seqnum + 2))
from l;

Here is a db<>fiddle.

Using Column in 'Case when' without including in Group By - or use partition over

This should be your query:

with taxes as (
select invoiceNo,
       round(sum(case when taxname like 'State%' or taxname like 'Local%' then taxpercentile else 0 end), 2) 'State+Local', 
       round(sum(case when taxname like 'National%' then taxpercentile else 0 end), 2) 'National',
       round(sum(case when taxname like 'State%' or taxname like 'Local%' then taxamount else 0 end), 2) 'State+Local_Amount',
       round(sum(case when taxname like 'National%' then taxamount else 0 end), 2) 'National_Amount'
from tableb
group by invoiceno
)
    select a.*, t.*
      from tablea a
inner join taxes t 
        on a.invoiceno = t.invoiceno

You can check it in SQLFiddle.

How to concatenate two rows and sort in according to a column in SQL

SQL Fiddle

MS SQL Server 2014 Schema Setup:

Query 1:

DECLARE @TABLE TABLE(comp VARCHAR(20), vol VARCHAR(10), Volume INT, 
                     ID VARCHAR(20), Seg VARCHAR(10),name VARCHAR(10))
INSERT INTO @TABLE VALUES
('CSQ' ,  '9000'   ,   15000 ,'ANSD-09-MN-02','EWW','a'),
('CSY' ,  '11000'  ,   15000 ,'ANSD-09-MN-02','EWW','a'),
('CCB' , '0.020679',   15    ,'ANSD-09-W1-02','CID','W'),
('CID' , '0.868951',   15    ,'ANSD-09-W1-02','CID','W'),
('CSQ' , '9.919137',   15    ,'ANSD-09-W1-02','CID','W'),
('CSY' , '4.139181',   15    ,'ANSD-09-W1-02','CID','W')

SELECT t.Volume
      ,t.ID AS BatchID
      ,t.Seg
      ,t.name
      ,STUFF((SELECT '(' + comp + '-'+ Vol + ')'
              FROM @TABLE  
              WHERE Volume = t.Volume AND Seg = t.Seg
               AND ID = t.ID AND name = t.name 
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,0,'') AS Crucomp
FROM @TABLE t
GROUP BY t.Volume
      ,t.ID 
      ,t.Seg
      ,t.name
ORDER BY t.Volume DESC

Results:

| Volume |       BatchID | Seg | name |                    Crucomp           
|--------|---------------|-----|------|----------------------------------------------------------|
|  15000 | ANSD-09-MN-02 | EWW |    a |       (CSQ-9000)(CSY-11000) 
|     15 | ANSD-09-W1-02 | CID |    W | (CCB-0.020679)(CID-0.868951)(CSQ-9.919137)(CSY-4.139181) |

Concatenate rows bases on sequence number

That was a comment in the beginning, but I decided to prepare an answer out of this...

Are you aware, that there is no implicit sort order? it seems that you want to start a new group when the SeqNumber is a 1. So - in your sample - A and B are the first group, FKM the second and so on. But without an additional sort order or any kind of grouping key this set is not ordered!. This may return in any random order...

This was the approach with a grouping key using STRING_AGG (needs v2017+):

DECLARE @T AS TABLE
(
    GroupKey INT,
    SeqNumber INT, 
    Note VARCHAR(MAX),
    UserID INT
)

INSERT INTO @T (GroupKey,SeqNumber, Note, UserID) VALUES
(1, 1, 'A', 20),
(1, 2, 'B', 20),
(2, 1, 'F', 20),
(2, 2, 'K', 20),
(2, 3, 'M', 20),
(3, 1, 'X', 20),
(4, 1, 'Y', 20);

SELECT t.UserID
      ,STRING_AGG(Note,'') WITHIN GROUP(ORDER BY t.SeqNumber) AS ResultNotes
FROM @T t
GROUP BY t.GroupKey,t.UserID;

And this works in a version before 2017:

SELECT t.UserID
     ,(
        SELECT t2.Note AS [*]
        FROM @T t2
        WHERE t.GroupKey=t2.GroupKey
          AND t.UserID=t2.UserID
        ORDER BY t2.SeqNumber
        FOR XML PATH(''),TYPE
      ).value('.','nvarchar(max)') AS ResultNotes
FROM @T t
GROUP BY t.GroupKey,t.UserID

UPDATE: Create a GroupingKey out of a fix order

See this code how to create a grouping key, if the sort order is fixed (in my case by an IDENTITY-ID):

DECLARE @T AS TABLE
(
    ID INT IDENTITY,
    SeqNumber INT, 
    Note VARCHAR(MAX),
    UserID INT
)

INSERT INTO @T (SeqNumber, Note, UserID) VALUES
(1, 'A', 20),
(2, 'B', 20),
(1, 'F', 20),
(2, 'K', 20),
(3, 'M', 20),
(1, 'X', 20),
(1, 'Y', 20);

WITH Get1s AS --set a group key per "1"
(
    SELECT t.ID,ROW_NUMBER() OVER(ORDER BY ID) AS GroupKey
    FROM @T t
    WHERE t.SeqNumber=1
)
SELECT *
      ,(SELECT MAX(x.GroupKey) FROM Get1s x WHERE t.ID>=x.ID) AS GroupKey
FROM @T t;

In the next step you can use this grouping key in an approach like above...

How to display Text Unit only one time if it repeated for same Feature when do stuff?

TomC's answer is basically correct. However, when using this method with SQL Server, it is usually more efficient to get the rows in a subquery and then use stuff() in the outer query. That way, the values in each row are processed only once.

So:

SELECT PartID, DKFeatureName, CountParts,
       STUFF( (SELECT ' | ' + TextUnit  
               FROM #FinalTable b  
               WHERE b.PartID = a.PartID AND
                     b.DKFeatureName = a.DKFeatureName AND
                     StatusId = 3 
               FOR XML PATH('')
              ), 1, 3, ' ') as TextUnit
INTO #getUnitsSticky 
FROM (SELECT PartID, DKFeatureName, COUNT(*) as CountParts
      FROM #FinalTable a  
      GROUP BY PartID, DKFeatureName
      HAVING COUNT(*) > 1
     ) a;

This also removes the leading space from the concatenated result.

Which is the best way to form the string value using column from a Table with rows having same ID?

For XYZID = 1.

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB
              where XYZID = 1
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

For all rows in TableA:

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB as B
              where A.XYZID = B.XYZID
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
from TableA as A

converting normal query into fancy or partition/over query

Something like...?

   select client_id,accessioncount,COUNT(patient_id) PatientCount from 
    ( 
        select client_id,patient_id, count(accession_id) as accessioncount
        from F_ACCESSION_DAILY 
        group by CLIENT_ID,PATIENT_ID 
    ) a 
    group by CLIENT_ID,accessioncount

---

Related Topics