splitting a delimited field into separate columns in same record
I need to split a pipe-delimited field ('Y|N|Y|Y').
I found the recursive function listed here T-SQL: Opposite to string concatenation - how to split string into multiple records
but it creates the values into new records.
row field
1 Y
2 N
3 Y
4 N
I need to transform 'Y|N|Y|Y'
field
'Y|N|Y|Y'
into
field1 | field2 | field3 | field4
y N Y N
Can someone point me in the right direction? Would it make it easier if I said the number of values is fixed (it will probably be 8 values delimited in one field).
Update: A possible field value could be this (note 开发者_开发百科the blank value): 'Y|10|N|1||Y'
**EDIT: **Mine still works for the sample string
Here's an alternative answer, since you know the widths are a fixed length:
DECLARE @myString AS nvarchar(20) = 'Y|10|N|1||Y'
;WITH cte
AS
(
SELECT
KeyCol = @@IDENTITY,
CONVERT(XML,'<i>' + REPLACE(@myString, '|', '</i><i>') + '</i>') AS delimited_str
)
SELECT
[1] AS Field1,
[2] AS Field2,
[3] AS Field3,
[4] AS Field4,
[5] AS Field5,
[6] AS Field6,
[7] AS Field7,
[8] AS Field8
FROM(
SELECT
KeyCol,
ROW_NUMBER() OVER (partition by KeyCol order by KeyCol)as col_nbr,
x.i.value('.', 'VARCHAR(50)') AS delimited_VAL
FROM cte
CROSS APPLY delimited_str.nodes('//i') AS x(i)
) as PivotedDataTable
PIVOT
(MAX(delimited_VAL) FOR col_nbr IN
([1], [2], [3], [4], [5], [6], [7], [8])
) AS PivotTable;
EDIT: I knew I saw this somewhere before when I came across a similar problem: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/381e4164-f1e0-4b54-828f-2795d2cdcb3e/
If the widths of the fields are always fixed as well, i.e., 1 character (Y/N), then it's as simple as using the substring
function to get all the individual field values:
;with Data as (
select 'Y|N|Y|Y' as choices union
select 'Y|Y|Y|Y' as choices union
select 'Y|N|N|Y' as choices union
select 'Y|N|N|N' as choices union
select 'N|N|Y|N' as choices union
select 'Y|Y|N|Y' as choices
)
select
substring(choices, 1, 1) as field1,
substring(choices, 3, 1) as field2,
substring(choices, 5, 1) as field3,
substring(choices, 7, 1) as field4
from
Data
Output:
field1 field2 field3 field4
N N Y N
Y N N N
Y N N Y
Y N Y Y
Y Y N Y
Y Y Y Y
If you can't guarantee that the widths of the fields are the same, you can make use of charindex
and a helper table of field indexes to generate the output you are looking for. This becomes very verbose as the number of fields grows larger, but you should only have to write it once if the number of fields is going to be fixed:
;with Data as (
select 1 as id, 'Y|N|Y|Y' as choices union
select 2,'Y|Y|Y|Y' as choices union
select 3,'Y|No|N|Y' as choices union
select 4,'Yes|N|N|N' as choices union
select 5,'N|N|Yes|No' as choices union
select 6,'Y|Y|N|Yes' as choices
), Fields as (
select
id,
charindex('|', choices) as field1end,
charindex('|', choices, charindex('|', choices) + 1) as field2end,
charindex('|', choices, charindex('|', choices, charindex('|', choices) + 1) + 1) as field3end,
len(choices) + 1 as field4end
from
Data
)
select
substring(choices, 1, field1end - 1) as field1,
substring(choices, field1end + 1, field2end - field1end - 1) as field2,
substring(choices, field2end + 1, field3end - field2end - 1) as field3,
substring(choices, field3end + 1, field4end - field3end - 1) as field4
from
Data D
inner join
Fields F on D.id = F.id
Output:
field1 field2 field3 field4
Y N Y Y
Y Y Y Y
Y No N Y
Yes N N N
N N Yes No
Y Y N Yes
精彩评论