将多个字符串拆分为多列 [英] Split multiple string's into multiple columns

问题描述

我有以下三个不同的字符串，它们需要分成三个不同的列.

I have the following three different strings which needs to split into three different columns.

示例:

字符串 1:

Declare @str1 varchar(max) = 'A1,A2,A3'

字符串 2:

Declare @str2 varchar(max) = 'B1,B2,B3'

字符串 3:

Declare @str2 varchar(max) = 'C1,C2,C3'

注意我想将以上三个字符串存储到三个不同的列中.

NoteI want to store the above three strings into three different columns.

预期输出:

colA   colB   colC
------------------
A1     B1     C1
A2     B2     C2
A3     B3     C3

尝试:

SQL Fiddle:http://sqlfiddle.com/#!3/d41d8/41345

推荐答案

我知道它有点重，但它会工作

I know its a bit heavy but it will work

Declare @str1 varchar(max) = 'A1,A2,A3'
Declare @str2 varchar(max) = 'B1,B2,B3'
Declare @str3 varchar(max) = 'C1,C2,C3'

DECLARE @RowCount TINYINT
DECLARE @i        TINYINT = 0

DECLARE @Table AS TABLE

(
 colA  varchar(MAX)
,ColB varchar(MAX)
,ColC varchar(MAX)

)

SET @RowCount =  len(@str1) - len(replace(@str1, ',', ''))

WHILE(@i<=@RowCount)
BEGIN
    INSERT INTO @Table
    SELECT  LEFT(@str1,CHARINDEX(',',@str1+',',0)-1) AS colA
           ,LEFT(@str2,CHARINDEX(',',@str2+',',0)-1) AS colB
           ,LEFT(@str3,CHARINDEX(',',@str3+',',0)-1) AS colC

    SET @str1 = STUFF(@str1,1,CHARINDEX(',',@str1,0),'')
    SET @str2 = STUFF(@str2,1,CHARINDEX(',',@str2,0),'')
    SET @str3 = STUFF(@str3,1,CHARINDEX(',',@str3,0),'')

    SET @i = @i + 1

END

SELECT * FROM @Table

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