如何“放大”到“显示”。 （反规范化/合并）多列成一列？ [英] How to "Implode" (de-normalize/concat) multiple columns into a single column?

问题描述

我有一个查询，输出如下内容：

I have a query which outputs something like this:

+-------+----+--------------+
| F_KEY | EV | OTHER_COLUMN |
+-------+----+--------------+
| 100   | 1  | ...          |
| 100   | 2  | ...          |
| 150   | 2  | ...          |
| 100   | 3  | ...          |
| 150   | 4  | ...          |
+-------+----+--------------+

我确定我已经看到了一个聚合函数，可以将其转换为（使用 GROUP BY F_KEY ） ：

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)     | ...          |
+-------+------------+--------------+

意味着，它以某种方式将 EV 的值内含到一个字段中。我怎样才能做到这一点？不幸的是，我不记得该函数的名称。

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.

我正在使用SQL Server。

I'm using SQL Server.

这是我的查询的简化：

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!

推荐答案

这是最好的串联方法，它不会像其他XML方法一样扩展特殊字符：

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

输出：

HeaderValue ChildValues
----------- ---------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

这篇关于如何“放大”到“显示”。 （反规范化/合并）多列成一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！