How to do this:

How to do this:

I have a table ID, Desc

ID, Desc

1, xyz|zyx|abc

2, xbz|zvx|abc

3, xaz|zcx|dbc

How can you convert this to

ID,Desc

1 xyz

1 zyx

1 abc

2, xbz

2 zvx

2 abc

3, xaz

3 zcx

3 dbc

select

id,

part

from

(

select

id,

substring(desc,1,3) part,

1 seqno

from <table>

union

select

id,

substring(desc,5,3) part,

2 seqno

from <table>

union

select

id,

substring(desc,9,3) part,

3 seqno

from <table>

) tmp

order by id, seqno

;

Dan

|||

Another method that might work might be something like:

declare @.stuff table
( ID int,
[Desc] varchar(15)
)
insert into @.stuff
select 1, 'xyz|zyx|abc' union all
select 2, 'xbz|zvx|abc' union all
select 3, 'xaz|zcx|dbc'
--select * from @.stuff

select id,
case when rid = 1
then left ([Desc], charindex('|', [Desc])-1)
when rid = 2
then substring ([Desc],
charindex('|', [Desc]) + 1,
len([Desc]) - charindex('|', reverse([Desc]))
- charindex('|', [Desc]))
when rid = 3
then right([desc], charindex('|', reverse([Desc])) - 1)
end
as [Desc]
from @.stuff
cross join
(select 1 as rid union all select 2 union all select 3
) b

/*
id Desc
--
1 xyz
1 zyx
1 abc
2 xbz
2 zvx
2 abc
3 xaz
3 zcx
3 dbc
*/

|||

Kent,

Your approach has the wonderful benefit of not requiring any fixed-length aspect of the strings between the "vertical bars".

Thanks.

Dan

|||

First off, just to note, you should never store data like this. You should store it in rows, and then (if needed) rotate the data for display like this...

Assuming you are converting data to put in your freshly designed, first normal form compliant table, here is another technique (2005 only) that you can use to expand any number of rows, rather than just 3. It supports up to 1000 character values for the delimited list:

Code Snippet

drop table expandColumnsExample
go
create table expandColumnsExample
( expandColumnsExampleId int primary key,
ExpandMe varchar(1000)
)
insert into expandColumnsExample
select 1, 'xyz|zyx|abc' union all
select 2, 'xbz|zvx|abc' union all
select 3, 'xaz|zcx|dbc|fbc|rbc'
go

--use CTE's to build a sequence set of rows from 0 to 999 (it is easy to expand)

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) as i
FROM digits AS D1, digits AS D2, digits AS D3
)
select expandColumnsExample.expandColumnsExampleId, cast(makeRows.rowValue as varchar(10)) as rowValue
from expandColumnsExample
cross apply ( --cross apply lets you send values from the first set

--to the derived table:

SELECT substring('|' + expandColumnsExample.expandMe + '|', i + 1,
charindex('|', '|' + expandColumnsExample.expandMe + '|',

i + 1) - i - 1) AS rowValue
FROM sequence
WHERE i <= len('|' + expandColumnsExample.expandMe + '|') - 1
AND substring('|' + expandColumnsExample.expandMe + '|',

i, 1) = '|') as makeRows
order by expandColumnsExample.expandColumnsExampleId

Returns:

expandColumnsExampleId rowValue
- -
1 xyz
1 zyx
1 abc
2 abc
2 zvx
2 xbz
3 xaz
3 fbc
3 zcx
3 rbc
3 dbc

Note, this is based on techiniques I found in Erland's excellent articles: http://www.sommarskog.se/arrays-in-sql.html

|||

I try to impliment cross apply by UDF / user defined function.

table: t1560602

function: f1560602

Code Snippet

-- CREATE TABLE
CREATE TABLE [dbo].[t1560602]
(
[ID] [int] NOT NULL,
[Desc] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_t1560602] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY];
GO

-- INSERT DATA
INSERT INTO [t1560602] VALUES (1, N'xyz|zyx|abc');
INSERT INTO [t1560602] VALUES (2, N'xbz|zvx|abc');
INSERT INTO [t1560602] VALUES (3, N'xaz|zcx|dbc|fbc|rbc');
GO

-- CREATE FUNCTION
CREATE FUNCTION f1560602
(
@.id int
)
RETURNS @.result TABLE (ID int, [Desc] nvarchar(1000))
AS
BEGIN
declare @.Desc nvarchar(1000);
declare @.s nchar(1);
declare
@.p int,
@.q int,
@.d nvarchar(1000);

set @.s = N'|';

select
@.Desc = [Desc]
from t1560602
where
ID = @.ID;

if @.@.rowcount = 0
return;

if Len(@.Desc) = 0
return;

set @.q = 1;
set @.p = charindex(@.s, @.Desc, @.q);
while @.p > 0
begin
set @.d = substring(@.Desc, @.q, @.p - @.q);
insert into @.result (ID, [Desc])
values (@.id, @.d);
set @.q = @.p + 1;
set @.p = charindex(@.s, @.Desc, @.q);
end;
set @.d = substring(@.Desc, @.q, len(@.Desc) - @.q + 1);
insert into @.result (ID, [Desc])
values (@.id, @.d);

RETURN;
END

-- GET RESULT
select
t.ID, f.[Desc]
from t1560602 as t
cross apply f1560602(ID) as f