sql-server – 如何将多个列“内嵌”(去标准化/连接)到一个列中

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

我确信我已经看到了一个聚合函数,它将它(使用GROUP BY F_KEY)变成这样的东西：

+-------+------------+--------------+
| F_KEY | ?          | OTHER_COLUMN |
+-------+------------+--------------+
| 100   | (1,2,3)  | ...          |
| 150   | (2,4)     | ...          |
+-------+------------+--------------+

意味着,它以某种方式将EV的价值“内爆”到一个单一的领域.我怎样才能做到这一点？不幸的是,我不记得该功能的名称.

我正在使用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

任何想法都表示赞赏！

解决方法

--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,'B<&>B')
insert into @YourTable VALUES (3,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,'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)'),''
              ) 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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!