May 4, 2010 at 8:13 am
Hi People,
I need some help on a grid i am trying to populate.
The grid column is called Correspondence which is populated with a comma seperated value. e.g. 1,2,3,4
Each of the values in the comma seperated column relates to a lookup table e.g.
ID Name
1 cr1
2 cr2
3 cr3
4 cr4
How do I get the following result in the grid
cr1,cr2,cr3,cr4 instead of 1,2,3,4 in the Correspondence column that is used in the grid
hope that makes sense.
Kind regards
Philip
May 4, 2010 at 8:27 am
CREATE FUNCTION [dbo].[uf_utl_SplitNString]
(
@InStr nvarchar(4000) = null ,
@token nvarchar(4000) = ','
)
RETURNS @RtnElement TABLE ( item nvarchar(4000))
AS
BEGIN
declare @rec_no int
declare @pos int, @tokenlen int
declare @ThisStr nvarchar(4000)
declare @SqlStr nvarchar(4000), @debug bit
/*
********************************************************************************
** Variables Initialization **
********************************************************************************
*/
set @InStr = replace(@InStr,'"','""') -- to escape the single/double quote
/*
********************************************************************************
** FUNCTION Body **
********************************************************************************
*/
if (len(@InStr)+len(@token) < 4050) and len(@InStr) <> 0
begin
set @tokenlen = case @token when ' ' then 1 else len(@token) end
set @InStr = @InStr + @token
while len(@InStr) > 0
begin
set @pos = charindex(@token, @InStr )
set @ThisStr = left(@InStr, @pos -1 )
set @InStr = substring(@InStr, @pos+@tokenlen,4000)
insert @RtnElement (item ) select case when len(@ThisStr) > 0 then @ThisStr else null end
end
end
return
end
select item , 'cr' + item from dbo.uf_utl_SplitNString ('1,2,3,4', ',')
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2010 at 8:41 am
Thanks for that, but not quite what I was after.
I need the output to also be comma seperated but with the name value from a seperate table
Let me try to explain better
Grid View
Column 1 Column 2
1,2,3,4 CR1,CR2,CR3,CR4
1,3,4,5 CR1,CR3,CR4,CR5
1,5,4,3 CR1,CR5,CR4,CR3
Look up table
Column 1 Column 2
1 CR1
2 CR2
3 CR3
4 CR4
5 CR5
Column 1 in the grid view shows a comma seperated value popualted by a system I have, but I need a seperated column that shows the Column 2 value comma seperated.
May 4, 2010 at 11:03 am
Is it really as simple as every table having CR and the number, or could you have a situation where say:
11 ties to CR34
or
12 ties to TM12
etc.
Also, are all your Comma delimited lists accurate or do you need to account for things like
1,,3
,1
etc.
May 4, 2010 at 11:08 am
Depending on the answers to my above two questions, this might be able to be handled with a lookup table and some dynamic SQL.
IE:
1,2,3,4,5
and a lookup table such as:
Value LUT
1 AB1
2 CR2
3 CM3
4 Blah45
5 Something5
That way you don't have to parse the list, you can just use an IN and then concatenate the new list.
May 4, 2010 at 11:09 am
The values are not set to a standard so will change from CR to LM or TR or ?? and will not always be followed by a numeric so could be TYRETY for instance.
There could be an example where the the comma seperated string has a single value like 1 but never 1,,2
Many thanks
Philip
May 4, 2010 at 11:33 am
Here's an example that does parse the list instead of using dynamic SQL. Note the way that I supplied sample data and create table statements. In the future, please do this with your posts to save us time.
A walkthrough of an easy way to do this is in my signature.
Other things you might need to know. The function I used for this can be found here[/url].
That function relies on a tally table. If you don't have one, see the article in my signature on Tally Tables to create one.
This whole thing could be done more 'easily' with dynamic SQL a loop and a variable, but this method is (in my opinion) "better" and likely quite a bit more efficient.
CREATE TABLE #A(
CSLvarchar(100)
)
INSERT INTO #A(CSL)
SELECT '1' UNION ALL SELECT '1,2,3' UNION ALL SELECT '1,2,4,5'
CREATE TABLE #LUT(
InValvarchar(10),
LUTValvarchar(20)
)
INSERT INTO #LUT
SELECT '1','CR1' UNION ALL
SELECT '2','CM2' UNION ALL
SELECT '3','LR3' UNION ALL
SELECT '4','TP4' UNION ALL
SELECT '5','MU5'
SELECT STUFF((
SELECT ',' + L.LUTVal
FROM #LUT L
INNER JOIN Util.dbo.TVF_TallySplit(',',A.CSL) B ON L.InVal = B.ListValue
FOR XML PATH('')),1,1,'') NewList
FROM #A A
DROP TABLE #a
DROP TABLE #LUT
May 4, 2010 at 2:29 pm
Seth, you are a life saver.
Many thanks that works a treat.
:smooooth::-D:-D:smooooth:
May 4, 2010 at 4:58 pm
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply