getting labels - function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting labels - function

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-08-21 : 09:51:08

hi,

i have following situation:


create table value
(id int
,list nvarchar(30)
,int smallint)

create table value_label
(int smallint
,label nvarchar(10))

insert into value_label
select 1, '111' union all
select 4, '4444' union all
select 5, '55555' union all
select 6, '666666' union all
select 10, '101010'

insert into value
select 1, '4;5;6;', 3 union all
select 2, '1;5;', 2 union all
select 3, '1;', 1 union all
select 4, '5;10;1;', 3

and if i run

select * from value

i get

id list int
----------- ------------------------------ ------
1 4;5;6; 3
2 1;5; 2
3 1; 1
4 5;10;1; 3

(4 row(s) affected)

but i would like to have export in column list with labels from label table,
so it would look like:

id list int
----------- ------------------------------ ------
1 4444;55555;666666; 3
2 111;55555; 2
3 111; 1
4 55555;101010;111; 3

(4 row(s) affected)

Anybody have function or idea how to do it.

i'm using SQL 2000.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:58:46

Why are you tables designed in this way? Have you read about normalisation?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-21 : 10:07:28

[code]SELECT v.id,
list = STUFF((SELECT TOP 100 PERCENT ',' + l.label
FROM value x
CROSS apply dbo.fnParseList(';', x.list) f
INNER JOIN value_label l ON f.Data = l.int
WHERE x.id = v.id
ORDER BY f.RowID
FOR XML PATH('')), 1, 1, '') ,
v.int
FROM value v[/code]

EDIT : DAMN ! just realize OP need it on SQL 2000. This is for 2005

KH
[spoiler]Time is always against us[/spoiler]

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-08-21 : 10:11:31

visakh16: don't ask :) it's beyond my power.

Khtan: thank you....got solution for SQL 2000?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources