January 6, 2015 at 3:49 am
Hi Team,
I have Table with two columns
id | codes
------------
001 | a,b,c
002 | e,g,t
003 | r,t,y,e
codes column contain codes
descriptions of codes are available in another table
how to create a view to display id and code with descriptions
eg:
id | codes
------------
001 | art,byt,ctet
002 | efe,ggtt,tte
Please suggest..
January 6, 2015 at 3:58 am
Do you have any control over the structure of the database? If you do, I would strongly recommend changing your table so it looks something like this:
001 a
001 b
001 c
002 e
...
If you can't do that, you'll need to use a splitter function to separate the codes out. If you search this site, you'll find some help on doing that. Please post (for both tables) DDL in the form of INSERT statements and sample data in the form of INSERT statements.
John
January 6, 2015 at 4:54 am
create function splitstring
(
@input nvarchar(max),
@character char(1)
)
returns @output table (
stg nvarchar(1000)
)
as
begin
declare @startindex int, @endindex int
set @startindex = 1
if substring(@input, len(@input) - 1, len(@input)) <> @character
begin
set @input = @input + @character
end
while charindex(@character, @input) > 0
begin
set @endindex = charindex(@character, @input)
insert into @output(stg)
select substring(@input, @startindex, @endindex - 1)
set @input = substring(@input, @endindex + 1, len(@input))
end
return
end
go
-- drop function splitstring
SELECT stg
FROM dbo.SplitString('C,X,M', ',')
January 6, 2015 at 4:55 am
using above function, but how to include in view..
January 6, 2015 at 4:56 am
Don't use that, it'll be horribly slow. Look for the delimited8kSplit function on this site.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 5:57 am
Hi Team,
If there any possiblity to replace below char's in code column with string in another table.
id | codes
------------
001 | a,b,c
002 | e,g,t
003 | r,t,y,e
without using split function.
January 6, 2015 at 7:12 am
Table 1:
id | codes
----------------
001 | a,b,c
002 | e,g,t
003 | r,t,y,e
eg:
Desc_id | Code | Desc
--------------------------
001 | a | artb
002| b | byt
003| c | ctet
004| d | dtet
i need output like below
id | codes
-------------------
001 | artb,byt,ctet
January 6, 2015 at 7:16 am
You'll need to normalise your table design (attributes should be atomic and a comma-delimited string is not atomic) or use a split function.
As John requested
Please post (for both tables) DDL in the form of CREATE statements and sample data in the form of INSERT statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 7:20 am
Minnu (1/6/2015)
Hi Team,
If there any possiblity to replace below char's in code column with string in another table.
id | codes
------------
001 | a,b,c
002 | e,g,t
003 | r,t,y,e
without using split function.
Yes there is, but first:
John Mitchell-245523 (1/6/2015)
Please post (for both tables) DDL in the form of INSERT statements and sample data in the form of INSERT statements.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply