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
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;
How to group-concatenate multiple columns?
Just an idea:
DECLARE @t TABLE (
Customer VARCHAR(50),
Product VARCHAR(50),
Method VARCHAR(50),
INDEX ix CLUSTERED (Customer)
)
INSERT INTO @t (Customer, Product, Method)
VALUES
('John', 'Computer', 'Credit'),
('John', 'Mouse', 'Cash'),
('Will', 'Computer', 'Credit'),
('Will', 'Mouse', 'Cash'),
('Will', 'Speaker', 'Cash'),
('Todd', 'Computer', 'Credit')
SELECT t.Customer
, STUFF(CAST(x.query('a/text()') AS NVARCHAR(MAX)), 1, 2, '')
, STUFF(CAST(x.query('b/text()') AS NVARCHAR(MAX)), 1, 2, '')
FROM (
SELECT DISTINCT Customer
FROM @t
) t
OUTER APPLY (
SELECT DISTINCT [a] = CASE WHEN id = 'a' THEN ', ' + val END
, [b] = CASE WHEN id = 'b' THEN ', ' + val END
FROM @t t2
CROSS APPLY (
VALUES ('a', t2.Product)
, ('b', t2.Method)
) t3 (id, val)
WHERE t2.Customer = t.Customer
FOR XML PATH(''), TYPE
) t2 (x)
Output:
Customer Product Method
---------- -------------------------- ------------------
John Computer, Mouse Cash, Credit
Todd Computer Credit
Will Computer, Mouse, Speaker Cash, Credit
Another idea with more performance benefits:
IF OBJECT_ID('tempdb.dbo.#EntityValues') IS NOT NULL
DROP TABLE #EntityValues
DECLARE @Values1 VARCHAR(MAX)
, @Values2 VARCHAR(MAX)
SELECT Customer
, Product
, Method
, RowNum = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY 1/0)
, Values1 = CAST(NULL AS VARCHAR(MAX))
, Values2 = CAST(NULL AS VARCHAR(MAX))
INTO #EntityValues
FROM @t
UPDATE #EntityValues
SET
@Values1 = Values1 =
CASE WHEN RowNum = 1
THEN Product
ELSE @Values1 + ', ' + Product
END
, @Values2 = Values2 =
CASE WHEN RowNum = 1
THEN Method
ELSE @Values2 + ', ' + Method
END
SELECT Customer
, Values1 = MAX(Values1)
, Values2 = MAX(Values2)
FROM #EntityValues
GROUP BY Customer
But with some limitations:
Customer Values1 Values2
------------- ----------------------------- ----------------------
John Computer, Mouse Credit, Cash
Todd Computer Credit
Will Computer, Mouse, Speaker Credit, Cash, Cash
Also check my old post about string aggregation:
http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server
How to concatenate many rows with same id in sql?
In SQL-Server you can do it in the following:
QUERY
SELECT id, displayname =
STUFF((SELECT DISTINCT ', ' + displayname
FROM #t b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id
TEST DATA
create table #t
(
id int,
displayname nvarchar(max)
)
insert into #t values
(1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')
OUTPUT
id displayname
1 Editor, Reviewer
7 Editor, EIC, Reviewer
19 Editor, EIC, Reviewer
How to use GROUP BY to concatenate strings in MySQL?
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
From the link above, GROUP_CONCAT
: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
Group Concatenate Strings (Rows) in BigQuery
Do each unnest separately:
Does aggregation work?
SELECT STRING_AGG(item.abc, ',')
uniqueid, variable2, cust.variable1, purch.variable2
FROM mydata.order LEFT JOIN
UNNEST(purchases) as purch
ON true LEFT JOIN
UNNEST(codes_abs) as cod
ON true LEFT JOIN
UNNEST(cod.try_items) as item
ON true
GROUP BY uniqueid, variable2, cust.variable1, purch.variable2;
Concat single column fields using GROUP BY
Suppose you have a table test
as follows:
select id, val from test order by id, val;
2 aa
2 bb
1 bb
1 aa
You can use the HIVE
function collect_set
:
select id, collect_set(val) from test group by id;
1 ["aa","bb"]
2 ["bb","aa"]
But note that collect_set
returns a set of objects with duplicate elements eliminated.
You can find more details at the Language Manual Wiki.
Concatenate across columns and rows using group and listagg in Oracle SQL
Use the following query where you will directly get concatenated column values:
SELECT
"GROUP",
LISTAGG(VAL_1 || VAL_2 || VAL_3)
WITHIN GROUP(ORDER BY VAL_1) AS "TEXT"
FROM DATA
GROUP BY "GROUP";
Note: Do not use oracle reserved keywords as the column names. Here GROUP
is the oracle reserved keyword.
Cheers!!
Related Topics
How to Split a Single Column Values to Multiple Column Values
Multiplication Aggregate Operator in Sql
How to Order by With Union in Sql
How to Create a Sequence in MySQL
Sqlite - How to Join Tables from Different Databases
Simulate Create Database If Not Exists For Postgresql
How to Install Freetds in Linux
SQL Server 2008: How to Grant Privileges to a Username
Why Is SQL Server Losing a Millisecond
Passing an Array of Parameters to a Stored Procedure
SQL Server - in Clause With a Declared Variable
Get the Week Start Date and Week End Date from Week Number