I have a query which outputs something like this:
+-------+----+--------------+
| F_KEY | EV | OTHER_COLUMN |
+-------+----+--------------+
| 100 | 1 | ... |
| 100 | 2 | ... |
| 150 | 2 | ... |
| 100 | 3 | ... |
| 150 | 4 | ... |
+-------+----+--------------+
I'm sure that I've seen an aggregation function which turns it (using GROUP BY F_KEY
) into something like this:
+-------+------------+--------------+
| F_KEY | ? | OTHER_COLUMN |
+-------+------------+--------------+
| 100 | (1, 2, 3) | ... |
| 150 | (2, 4) | ... |
+-------+------------+--------------+
Means, it somehow "implodes" the values of EV
together into one single field. How can I do this? Unfortunately, I don't remember the function's name.
I'm using SQL Server.
This is a simplification of my query:
SELECT
F_KEY,
EV,
OTHER_COLUMN
FROM
TABLE1
JOIN
TABLE2 ON F_KEY = TABLE2.ID
WHERE
EVENT_TIME BETWEEN '2011-01-01 00:00:00.000' AND '2011-12-31 23:59:59.999'
ORDER BY
EVENT_TIME ASC
Any idea is appreciated!
here is the best concatenation method, it will not expand special characters like other XML methods:
--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
t1.HeaderValue
,STUFF(
(SELECT
', ' + t2.ChildValue
FROM @YourTable t2
WHERE t1.HeaderValue=t2.HeaderValue
ORDER BY t2.ChildValue
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
) AS ChildValues
FROM @YourTable t1
GROUP BY t1.HeaderValue
OUTPUT:
HeaderValue ChildValues
----------- ---------------
1 CCC
2 AAA, B<&>B
3 <br>, A & Z
(3 row(s) affected)
You can do it easily enough with a function: Assumes you are going to be searching the same column/table all the time. Dynamic SQL needed if you want to be able to vary the columns/tables
CREATE FUNCTION [dbo].[fn_recursion]
(@F_KEY int)
RETURNS varchar(2000) AS
BEGIN
DECLARE @ReturnVal Varchar(2000)
SELECT @ReturnVal = COALESCE(@ReturnVal + ', ', '') + EV
FROM TABLE2
WHERE @F_KEY = @F_KEY
RETURN ISNULL(@ReturnVal,'')
END
GO
SELECT
F_KEY,
EV = [dbo].[fn_recursion](F_KEY),
OTHER_COLUMN
FROM
TABLE1
JOIN
TABLE2 ON F_KEY = TABLE2.ID
WHERE
EVENT_TIME BETWEEN '2011-01-01 00:00:00.000' AND '2011-12-31 23:59:59.999'
ORDER BY
EVENT_TIME ASC
GO
DROP FUNCTION [dbo].[fn_recursion]
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With