SQL Server 将 CSV 拆分为多行 [英] SQL Server split CSV into multiple rows

问题描述

我意识到以前有人问过这个问题，但由于某种原因我无法让它发挥作用.

I realize this question has been asked before, but I can't get it to work for some reason.

我正在使用 这个 SQL 团队线程(第二个帖子)和以下查询.

I'm using the split function from this SQL Team thread (second post) and the following queries.

--This query converts the interests field from text to varchar
select
    cp.id
    ,cast(cp.interests as varchar(100)) as interests
into #client_profile_temp
from
    client_profile cp

--This query is supposed to split the csv ("Golf","food") into multiple rows            
select
    cpt.id
    ,split.data
from
    #client_profile_temp cpt
    cross apply dbo.split(
    cpt.interests, ',') as split  <--Error is on this line

但是我得到了一个

Incorrect syntax near '.'

我在上面标记的错误.

最后，我想要

ID              INTERESTS
000CT00002UA    "Golf","food"

成为

ID              INTERESTS
000CT00002UA    "Golf"
000CT00002UA    "food"

我使用的是 SQL Server 2008，我的回答基于这个 StackOverflow 问题.我对 SQL 还很陌生，所以我也很感激任何其他智慧之词.

I'm using SQL Server 2008 and basing my answer on this StackOverflow question. I'm fairly new to SQL so any other words of wisdom would be appreciated as well.

推荐答案

from
    #client_profile_temp cpt
    cross apply dbo.split(
    #client_profile_temp.interests, ',') as split  <--Error is on this line

我认为在给它一个别名后显式命名 #client_profile_temp 是一个问题，请尝试制作最后一行:

I think the explicit naming of #client_profile_temp after you gave it an alias is a problem, try making that last line:

    cpt.interests, ',') as split  <--Error is on this line

编辑你说

我做了这个改变，但它没有改变任何东西

I made this change and it didn't change anything

尝试粘贴下面的代码(到一个新的 SSMS 窗口)

Try pasting the code below (into a new SSMS window)

create table #client_profile_temp
(id int,
interests varchar(500))

insert into  #client_profile_temp
values
(5, 'Vodka,Potassium,Trigo'),
(6, 'Mazda,Boeing,Alcoa')

select
   cpt.id
  ,split.data
from
    #client_profile_temp cpt
    cross apply dbo.split(cpt.interests, ',') as split 

看看它是否如你所愿；我正在使用 sql server 2008，这对我来说很有效，可以得到我认为你想要的结果.

See if it works as you expect; I'm using sql server 2008 and that works for me to get the kind of results I think you want.

当您说我进行了更改"时，您是否有可能只是更改了存储过程但尚未运行它，或者更改了创建存储过程的脚本但尚未运行该脚本，诸如此类?正如我所说，它似乎对我有用.

Any chance when you say "I made the change", you just changed a stored procedure but haven't run it, or changed a script that creates a stored procedure, and haven't run that, something along those lines? As I say, it seems to work for me.

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