Concatenate One Field After Group By

GROUP BY to combine/concat a column

SELECT
     [User], Activity,
     STUFF(
         (SELECT DISTINCT ',' + PageURL
          FROM TableName
          WHERE [User] = a.[User] AND Activity = a.Activity
          FOR XML PATH (''))
          , 1, 1, '')  AS URLList
FROM TableName AS a
GROUP BY [User], Activity

SQLFiddle Demo

Group by in SQL Server and concatenate records in one column

You can use STUFF and GROUP BY with MIN,MAX,SUM aggregation as below and in your example paid amount is in 5 digit and amount in 6 digit so it can't be zero in balance. In my example I made 6 digits paid amount to match with your expected result but you should correct and use it as needed

DECLARE @sales TABLE(company VARCHAR(50), 
    ref INT, 
    Tag INT, 
    event_date DATE, 
    sale_price INT, 
    Amount  INT,  
    Receipt_No VARCHAR(50), 
    Paid INT, 
    Balance INT)

INSERT INTO @sales VALUES
('PRco Ltd',123,0311,'03-10-2018',610000,610000,'R19A0000761',500000,11000),
('PRco Ltd',123,0311,'03-10-2018',610000,610000,'R19A0000912',110000,0)

SELECT s.company, 
    s.ref, 
    s.Tag, 
    s.event_date,
    MAX(s.sale_price) sale_price,
    MAX(s.amount) amount,
    MAX(s1.receipt) receipt,
    SUM(s.paid) paid,
    (MAX(s.amount)-SUM(s.paid)) balance
FROM @sales s
OUTER APPLY (
            select stuff(
                (select ',' + s1.Receipt_No 
                from @sales s1
                where s1.company = s.company
                AND s1.ref = s.ref
                AND s1.Tag = s.Tag
                AND s1.event_date = s.event_date
                for xml path('')
                )
            , 1, 1, '') receipt
        ) s1
GROUP BY s.company, s.ref, s.Tag, s.event_date

OUTPUT:

company     ref Tag event_date  sale_price  amount  receipt                 paid    balance
PRco Ltd    123 311 2018-03-10  610000      610000  R19A0000761,R19A0000912 610000  0

Concatenate one field after GROUP BY

Try this:

SELECT [CreatedTime]
      ,[DeletedTime]
      ,[DeletedBy]
      ,[ResourceId]
      ,[AccountId]
      ,STUFF((select ',' + raType.Type 
              from vwAllAssignments raType 
              where raType.AccountId = vwAllAssignments.AccountId and 
                    raType.ResourceId = vwAllAssignments.ResourceId and 
                    raType.DeletedBy is null 
              for xml path('')), 1,1,'') AS [Type]
FROM vwAllAssignments
GROUP BY [ResourceId], [AccountId], [CreatedTime], [DeletedTime], [DeletedBy]

And an index like this should be helpful.

create index IX_vwAllAssignments on vwAllAssignments(AccountId, ResourceId, DeletedBy) include(Type)

How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

Concatenate values that are grouped by a column

You have nothing linking your inner and outer references to [Table], and you also need to make the outer reference distinct. Finally you need to either have no column name within your subquery, or it needs to be [text()]

SELECT  [Code]
        ,[Ref]
        ,STUFF((SELECT DISTINCT [Value] AS [text()]
                FROM [Table] AS T2
                WHERE T1.Code = T2.Code -- LINK HERE
                AND T2.Ref = T2.Ref     -- AND HERE
                FOR XML PATH ('')
                ),1, 1,'') AS [Values]
FROM    [Table] AS T1
GROUP BY T1.Code, T1.Ref; -- GROUP BY HERE

As an aside, you do not need to use STUFF as you have no delimiter, STUFF is typically used to remove the chosen delimiter from the start of the string. So when you have a string like ,value1,value2,value3, STUFF(string, 1, 1, '') will replace the first character with '' leaving you with value1,value2,value3.

You should also use the value xquery method to ensure you are not tripped up by special characters, if you don't and you try an concatenate ">>" and "<<" you would not end up with ">><<" as you might want, you would get ">><<", so a better query would be:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = [Value]
                    FROM    [Table] AS t2
                    WHERE   T1.Code = T2.Code
                    AND     T2.Ref = T2.Ref
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1
GROUP BY t1.Code, t1.Ref;

ADDENDUM

Based on the latest edit to the question it appears as though your Value column is coming from another table, linked to the first table by Code. If anything this makes your query simpler. You don't need the JOIN, but you still need to make sure that there is an expression to link the outer table to the inner table your subquery. I am assuming that the rows are unique in the first table, so you probably don't need the group by either:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    [Table2] AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1;

WORKING EXAMPLE

CREATE TABLE #Table1 (Code CHAR(2), Ref VARCHAR(10));
INSERT #Table1 VALUES ('A1', 'Car'), ('B2', 'Truck'), ('C3', 'Van');

CREATE TABLE #Table2 (Code CHAR(2), Value VARCHAR(2));
INSERT #Table2
VALUES ('A1', 'A'), ('A1', '-'), ('A1', 'B'),
        ('B2', 'CC'), ('B2', 'D'), ('B2', '-'),
        ('C3', 'F'), ('C3', '-'), ('C3', 'G');

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    #Table2 AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    #Table1 AS T1;

Can I concatenate multiple MySQL rows into one field?

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Hive: How to concat one column by order after group by?

In a subquery before collect_set, distribute by id and sort by id, rank. Dataset will be distributed between reducers by id and sorted by rank before aggregation. See comments in the code.

Demo:

with demo_dataset as ( --Use your table instead of this CTE
select stack(4,
'001' , 'pass', 2,
'002' , 'fail', 3,
'001' , 'fail', 1,
'002' , 'pass', 1
) as (id,result,rank)
)

select id, concat_ws('-',collect_set(result))
from
(
select t.* 
  from demo_dataset t
distribute by id   --Distribute by grouping column
sort by id, rank   --Sort in required order
) s
group by id

Result:

id  results
001 fail-pass
002 pass-fail

Now if you change SORT: sort by id, rank desc you will get results ordered differently