英文:
Multiple comma separated values in columns into a single table
问题
I can help with the translation. Here's the content you provided translated into Chinese:
我有一个简单的表格
| 用户ID | 列2 | 列3 | 列4 | 列5 |
|---|---|---|---|---|
| 1 | 5,1234 | 3,8967 | 2,3245 | 7,5658 |
| 2 | 2,3245 | 5,5658 | 6,4567 | 20,9876 |
我需要将每个逗号分隔的列分离成相同的字段，就像这样：
| 用户ID | 参考ID | 数值 |
|---|---|---|
| 1 | 5 | 1234 |
| 1 | 3 | 8967 |
| 1 | 2 | 3245 |
| 1 | 7 | 5658 |
| 2 | 2 | 3245 |
| 2 | 5 | 5658 |
希望这有所帮助。
英文:
I have a simple table
| Userid | Col2 | Col3 | Col4 | Col5 |
|---|---|---|---|---|
| 1 | 5,1234 | 3,8967 | 2,3245 | 7,5658 |
| 2 | 2,3245 | 5,5658 | 6,4567 | 20,9876 |
I need to separate each comma separated column into the same fields like this:
| Userid | refid | value | |
|---|---|---|---|
| 1 | 5 | 1234 | |
| 1 | 3 | 8967 | |
| 1 | 2 | 3245 | |
| 1 | 7 | 5658 | |
| 2 | 2 | 3245 | |
| 2 | 5 | 5658 |
and so on. I have messed around with string_split, cross apply, etc, but I can't seem to get it. Any thoughts?
答案1
得分: 2
以下是翻译好的部分：
你可以将表格进行解压缩，以获得Col2, Col3, Col4, Col5作为单独的列，然后使用LEFT和RIGHT字符串函数来获得所需的输出。
WITH CTE AS
(
SELECT Userid, colx
FROM
(
SELECT Userid, Col2, Col3, Col4, Col5
FROM table_name
) T
UNPIVOT
(
colx FOR c IN (Col2, Col3, Col4, Col5)
) UP
)
SELECT Userid,
LEFT(colx, CHARINDEX(',', colx) - 1) ref,
RIGHT(colx, LEN(colx) - CHARINDEX(',', colx)) val
FROM CTE
ORDER BY Userid
你也可以直接使用它，不过CTE会使代码更清晰。
英文:
You can unpivot your table to get Col2, Col3, Col4, Col5 as a single column, then use LEFT and RIGHT string functions to get the desired output.
WITH CTE AS
(
SELECT Userid, colx
FROM
(
SELECT Userid, Col2, Col3, Col4, Col5
FROM table_name
) T
UNPIVOT
(
colx FOR c IN (Col2, Col3, Col4, Col5)
) UP
)
SELECT Userid,
LEFT(colx, CHARINDEX(',', colx) - 1) ref,
RIGHT(colx, LEN(colx) - CHARINDEX(',', colx)) val
FROM CTE
ORDER BY Userid
You may also use it directly without the CTE, but CTE makes it clearer.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论