将逗号分隔的字符串转换为 Teradata 中的单独行 [英] Turning a Comma Separated string into individual rows in Teradata

问题描述

我读了这篇文章:将逗号分隔的字符串转换为单独的行

非常喜欢这个解决方案:

And really like the solution:

SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

但是当我尝试在 Teradata 中应用该方法解决类似问题时，它不起作用.这是总结的错误代码:选择失败 3707:期望在 '.' 之间有一些东西和价值"关键字.那么该代码仅在 SQL Server 中有效吗?有人能帮我让它在 Teradata 或 SAS SQL 中工作吗?您的帮助将不胜感激！

But it did not work when I tried to apply the method in Teradata for a similar question. Here is the summarized error code: select failed 3707: expected something between '.' and the 'value' keyword. So is the code only valid in SQL Server? Would anyone help me to make it work in Teradata or SAS SQL? Your help will be really appreciated!

推荐答案

这是 SQL Server 语法.

This is SQL Server syntax.

在 Teradata 中有一个名为 STRTOK_SPLIT_TO_TABLE 的表 UDF，例如

In Teradata there's a table UDF named STRTOK_SPLIT_TO_TABLE, e.g.

SELECT * FROM dbc.DatabasesV AS db
JOIN 
 (
   SELECT token AS DatabaseName, tokennum
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, 'dbc,systemfe', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(128) CHARACTER SET UNICODE)
              ) AS d 
 ) AS dt
ON db.DatabaseName  = dt.DatabaseName
ORDER BY tokennum;

或者看我对此的回答 类似问题

这篇关于将逗号分隔的字符串转换为 Teradata 中的单独行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！