将一列拆分为多行 [英] Split one column into multiple rows

问题描述

谁能告诉我如何做到这一点?在某些情况下，我的表中的一列包含逗号分隔值.如果是这样，我需要为这些值创建新行.

Can anyone tell me how to accomplish this? A column in my table, in some instances, contains comma separated values. If it does, I need to create new rows for these values.

此外，例如，一个表包含 1 行 4 列 Col1 |Col2 |Col3 |Col4 具有以下值 A |乙 |C |分别为 1、2、3.因此，Col4 包含字符串 '1,2,3'，我需要分解逗号分隔的值并将它们放在自己的行上，这样表将包含 1 行，其中 1 2 和 3 位于自己的行上在 Col4.

Moreover, as an example, a table contains 1 row and 4 columns Col1 | Col2 | Col3 | Col4 with the following values A | B | C | 1,2,3 respectively. So, Col4 contains the string '1,2,3' and I need to break up the comma separated values and place them on lines of their own so the table would then contain 1 rows where 1 2 and 3 are on lines of their own in Col4.

推荐答案

我认为你可以这样做:

SELECT
    T.id, RIGHT(LEFT(T.csv,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1))))
FROM
    master..spt_values,
    your_table T
WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1
    AND
    (SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1)  = '') 

代码被无耻地从本网站窃取.

这篇关于将一列拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！