February 17, 2006 at 6:36 pm
1. I have a table simplified as below:
Col 1 type int: userID
Col 2 type ntext
So a sample data is:
Col 1: 281
Col 2: 2001, 4888, 24, 367
Col 2 is multi valued with 0 or any number of integer IDs separated by commas.
2. I have another table that is the parent of the codes in col 2 of the above table.So this table will show:
ID Description
24 Project1
367 Mama Mia
2001 Papa John
4888 Whatever
3. What I need is to construct a temp table from the 2 tables above. This temp table has this layout:
Col 1: UserID
Col 2: all relevant description(s)
sample data:
Col 1 Col 2
--------------------------------------------------------------------
281 Project1, MamaMia, PapaJohn, Whatever
309 <NULL>
310 SomeDescription1, SomeDescription2
Can I create this table with T-SQL script? If yes, how? Thanks
February 18, 2006 at 5:57 am
Yes. Here you go.
create table table1
(
UserIDint,
Datantext
)
insert into table1
select281, '2001, 4888, 24, 367'union all
select309, NULL
create table table2
(
IDint,
Descriptionsvarchar(100)
)
insert into table2
select 24, 'Project1'union all
select 367, 'Mama Mia'union all
select 2001, 'Papa John'union all
select 4888, 'Whatever'
create table #temp
(
UserIDint,
Descriptionsvarchar(500)
)
CREATE function dbo.csv2table(@Str varchar(7000))
returns @t table (value varchar(100))
as
begin
declare @i int;
declare @C varchar(100);
set @i = 1;
set @C = '';
while @i <= len(@Str)
begin
if substring(@Str,@i,1) = ','
begin
insert into @t
values (rtrim(ltrim(@c)))
set @C = ''
end
else
set @C = @C + substring(@Str,@i,1)
set @i = @i +1
end
return
end
create function dbo.csv2desc(@str varchar(7000))
returns varchar(7000)
as
begin
declare
@descvarchar(7000)
select@desc = isnull(@desc, '') + t2.Descriptions + ', '
fromdbo.csv2table(@str) t1 inner join table2 t2
ont1.value= t2.ID
return @desc
end
select *, dbo.csv2desc(convert(varchar(7000), Data))
from table1
February 18, 2006 at 9:30 am
Thank you, KH for sharing your knowledge and script code. That works like a charm.
February 18, 2006 at 9:51 am
KH,
What will have to change when the table in step 1 is laid out as below:
Col 1 (type Int) : UserID
Col 2-12(type ntext): string of integers.
So a sample data will look as below:
Col 1: 281
Col 2: 23, 25
Col 3: null
Col 4: 45, 288, 307, 4000
Col 5: 14
Col 6: null
Col 7: 12,15
Col 8: 10, 11, 23, 24
Col 9: etc...
Somehow I will have to interrogate all col2-12 to retrieve all the comma separated values for a given user ID.
Thank you much in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply