sql server - create sql view from comma separated values -
t-sql question: need build join 2 tables, on 1 of tables have aggregated data (comma separated values).
i have table - users have 3 columns: userid, defaultlanguage , otherlanguages.
the table looks this:
userid | defaultlanguage | otherlanguages --------------------------------------------- 1 | en | null 2 | en | it, fr 3 | fr | en, 4 | en | sp and on.
i have table have association between language code (en, fr, ro, it, sp) , language name:
langcode | languagename ------------------------- en | english fr | french | italian sp | spanish and on.
i want create view this:
userid | defaultlanguage | otherlanguages --------------------------------------------- 1 | english | null 2 | english | italian, french 3 | french | english, italian 4 | english | spanish and on.
in short, need view language code replaced language name.
any help, please?
several solutions of course can recreate table change data structure. 1. if language 2 digits:
select t1.userid, t2.languagename, isnull( t3.languagename, '') + isnull(', '+t4.languagename, '') + isnull( ', '+t5.languagename, '') otherlanguages table1 t1 inner join table2 t2 on t1.defaultlanguage = t2.langcode left join table2 t3 on left(t1.otherlanguages,2) = t3.langcode left join table2 t4 on case when len(replace(t1.otherlanguages, ' ', '')) > 3 substring( replace(t1.otherlanguages, ' ', ''), 4, 2) else null end = t4.langcode left join table2 t5 on case when len(replace(t1.otherlanguages, ' ', '')) > 6 substring( replace(t1.otherlanguages, ' ', ''), 7, 2) else null end = t5.langcode - use user-define function:
create function [dbo].[func_getlanguagename] (@planguagelist varchar(max))
returns varchar(max) as
begin
declare @alanguagelist varchar(max) = @planguagelist declare @alangcode varchar(max) = null declare @areturnname varchar(max) = null while len(@alanguagelist) > 0 begin if patindex('%,%',@alanguagelist) > 0 begin set @alangcode = rtrim(ltrim(substring(@alanguagelist, 0, patindex('%,%',@alanguagelist)))) set @alanguagelist = ltrim(substring(@alanguagelist, len(@alangcode + ',') + 1,len(@alanguagelist))) end else begin set @alangcode = @alanguagelist set @alanguagelist = null end select @areturnname = isnull( @areturnname + ', ' , '') + languagename table2 langcode=@alangcode end return(@areturnname) end
and use select
select userid, dbo.func_getlanguagename(defaultlanguage)defaultlanguage, dbo.func_getlanguagename(otherlanguages) otherlanguages table1 