June 8, 2010 at 2:04 am
Hi,
Suppose I have Table (TABLE1) with Two column, ID and EmployeeName
and the data in the table is
1 Ritesh
2 Bhatt
Now i have second Table (TABLE2) with two column,
SourceFieldChar and Value and the data in the Table
R X
i Y
t Z
So basically we have to read the TABLE1 EmployeeName Field character by character and fetch the related value from TABLE2 and update the TABLE1 with the fetched value.
e.g.
We have to read the second column value from TABLE1 that is 'Ritesh' character by character and should get the output it this way 'XYZ'.
So the final values of the TABLE1 is like that
1 Ritesh to (XYZ)
2 Bhatt to (NULL)
Currently we are doing this by using Cursor, but the performance is very slow because we have million of records in the tables.
So need help ASAP if possible please provide EOD.
SAMPLE:
declare @myfinalvalue VARCHAR(255)
declare @myvalue VARCHAR(255)
WHILE @COUNTER <= @WORD_COUNT (TABLE1 'Ritesh' Value)
BEGIN
Set @valtoprocess = 'Ritesh'
SELECT @myvalue = Value FROM TABLE2
WHERE SourceFieldChar = SUBSTRING(@valtoprocess, @COUNTER, 1)
@myfinalvalue = @myfinalvalue + @myvalue
Set @COUNTER = @COUNTER + 1
The above query is in a cursor which changes the value to process TABLE1 second column.
Regards,
Ritesh
June 8, 2010 at 2:14 am
Hello,
if you need a quick answer, please read this and post data in easily consumable format:
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
To get started, search for articles by Jeff Moden on how to use Tally table and how to split strings with good performance - eg. this: http://www.sqlservercentral.com/articles/T-SQL/62867/
June 8, 2010 at 3:29 am
Hi,
It was my first post, so i am sorry for that, but yes i will do the best next time 🙂
Thanks,
Ritesh
June 8, 2010 at 5:31 am
This should get you started:
DROP TABLE #TABLE1
CREATE TABLE #TABLE1 (ID INT, EmployeeName VARCHAR(20)) -- see SELECT TOP 20 in CROSS APPLY
INSERT INTO #TABLE1 (ID, EmployeeName)
SELECT 1, 'Ritesh' UNION ALL
SELECT 2, 'Bhatt' UNION ALL
SELECT 3, 'q'
DROP TABLE #TABLE2
CREATE TABLE #TABLE2 (SourceFieldChar CHAR(1), Value CHAR(1))
INSERT INTO #TABLE2 (SourceFieldChar, Value)
SELECT 'R', 'X' UNION ALL
SELECT 'i', 'Y' UNION ALL
SELECT 't', 'Z'
SELECT Names.ID, Names.EmployeeName, iTVF.Decode
FROM #TABLE1 Names
CROSS APPLY (
SELECT ',' + CONVERT(VARCHAR(20), ISNULL(o.value, '*'))
FROM (
SELECT InputName = SUBSTRING(Names.EmployeeName, n.n, 1)
FROM (SELECT TOP 20 n = ROW_NUMBER() OVER (ORDER BY Name) FROM master.dbo.syscolumns) n -- substitute your favourite row generator
WHERE n.n <= LEN(Names.EmployeeName)
) i
LEFT JOIN #TABLE2 o ON o.SourceFieldChar = i.InputName
FOR XML PATH (''), TYPE
) iTVF ([Decode])
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
June 8, 2010 at 6:05 am
CHECK THIS
DECLARE @Table1 TABLE (ID INT , Employee VARCHAR(50))
DECLARE @Table2 TABLE (SourceFieldChar CHAR(1),Val CHAR(1) )
INSERT INTO @Table1
SELECT 1,'Ritesh' UNION ALL
SELECT 2, 'Bhatt'
INSERT INTO @Table2
SELECT 'R','X' UNION ALL
SELECT 'i', 'Y' UNION ALL
SELECT 't', 'Z' UNION ALL
SELECT 'B', 'A' UNION ALL
SELECT 'h', 'B' UNION ALL
SELECT 'a', 'C' UNION ALL
SELECT 'e', 'D' UNION ALL
SELECT 's', 'E'
UPDATE @Table1
SET Employee = Decode
FROM (
SELECT EmpTable.ID, EmpTable.Employee, REPLACE(CONVERT(VARCHAR(50),fn.Decode),':','') Decode
FROM @Table1 EmpTable
CROSS APPLY (
SELECT ':' + CONVERT(VARCHAR(50), ISNULL(t2.Val, '*')) FROM
((
SELECT Emp = SUBSTRING(EmpTable.Employee, T.n, 1)
FROMTALLY T
WHERE T.N<= LEN(Employee)
) T1
INNER JOIN @Table2 T2
ONT1.Emp=T2.SourceFieldChar
)
FOR XML PATH(''),TYPE
) fn ([Decode])
) Tab
INNER JOIN @Table1 T1
ON Tab.ID=t1.ID
SELECT * from @table1
June 8, 2010 at 6:17 am
Gopi Muluka (6/8/2010)
CHECK THIS
DECLARE @Table1 TABLE (ID INT , Employee VARCHAR(50))DECLARE @Table2 TABLE (SourceFieldChar CHAR(1),Val CHAR(1) )
INSERT INTO @Table1
SELECT 1,'Ritesh' UNION ALL
SELECT 2, 'Bhatt'
INSERT INTO @Table2
SELECT 'R','X' UNION ALL
SELECT 'i', 'Y' UNION ALL
SELECT 't', 'Z' UNION ALL
SELECT 'B', 'A' UNION ALL
SELECT 'h', 'B' UNION ALL
SELECT 'a', 'C' UNION ALL
SELECT 'e', 'D' UNION ALL
SELECT 's', 'E'
UPDATE @Table1
SET Employee = Decode
FROM (
SELECT EmpTable.ID, EmpTable.Employee, REPLACE(CONVERT(VARCHAR(50),fn.Decode),':','') Decode
FROM @Table1 EmpTable
CROSS APPLY (
SELECT ':' + CONVERT(VARCHAR(50), ISNULL(t2.Val, '*')) FROM
((
SELECT Emp = SUBSTRING(EmpTable.Employee, T.n, 1)
FROMTALLY T
WHERE T.N<= LEN(Employee)
) T1
INNER JOIN @Table2 T2
ONT1.Emp=T2.SourceFieldChar
)
FOR XML PATH(''),TYPE
) fn ([Decode])
) Tab
INNER JOIN @Table1 T1
ON Tab.ID=t1.ID
SELECT * from @table1
Imitation is the sincerest form of flattery 😛
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
August 13, 2010 at 6:19 am
Hi,
I am also looking for same kind of query.
If you have got the solution/query. Please share.
Thanks,
Deepak
August 13, 2010 at 6:24 am
deepak.khandelwal (8/13/2010)
If you have got the solution/query. Please share.Thanks,
Deepak
Yep; if you take the ears off a rabbit, you got a draught excluder.
None of us here are psychic:-P
How about starting a new thread and providing a full desription of what you are aiming to do.
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
August 14, 2010 at 12:23 am
Hello friend,
Kindly try below one.
DECLARE @Table1 TABLE (ID INT , Employee VARCHAR(50))
DECLARE @Table2 TABLE (SourceFieldChar CHAR(1),Val CHAR(1) )
INSERT INTO @Table1
SELECT 1,'Ritesh' UNION ALL
SELECT 2, 'Bhatt'
INSERT INTO @Table2
SELECT 'R','X' UNION ALL
SELECT 'i', 'Y' UNION ALL
SELECT 't', 'Z' UNION ALL
SELECT 'B', 'A' UNION ALL
SELECT 'h', 'B' UNION ALL
SELECT 'a', 'C' UNION ALL
SELECT 'e', 'D' UNION ALL
SELECT 's', 'E'
declare @count table(cn int)
Insert into @count
Select 1 union all
Select 2 union all
Select 3 union all
Select 4 union all
Select 5 union all
Select 6 union all
Select 7 union all
Select 8 union all
Select 9 union all
Select 10 union all
Select 11 union all
Select 12 union all
Select 13 union all
Select 14 union all
Select 15 union all
Select 16 union all
Select 17 union all
Select 20 union all
Select 21 union all
Select 22 union all
Select 23 union all
Select 24
;with cte
as
(
SELECT Id,Employee,emp,result from @table1 t
Cross Apply
(
Select emp=Substring(t.Employee,t2.cn,1) from @count t2
Where t2.cn<=len(t.Employee)
)s
Cross apply
(
Select result=t3.val from @table2 t3 where t3.SourceFieldChar=s.emp
)s1
),cte2
as
(
Select * from cte t
Cross apply
(
select STUFF((select CAST( result as varchar(max)) from cte t2 where t.id = t2.id order by t2.id,t2.employee for XML path('')),1,0,'') as TotalResult
)dd
)
Select distinct id,employee,TotalResult from cte2 order by id
Let me know that, do u need any further clariffication
August 16, 2010 at 4:00 am
Hi,
Thanks for the Reply!!
It is really helpful. But here along with this I am having 2 more requirement as below:
1. If I am using " SELECT 1,'Ritesh Bhatt' UNION ALL " statement instead of "SELECT 1,'Bhatt' UNION ALL , Then it is not returning proper output. i.e. instead of space it is using B's translation character from @Table2 and so on. So I want space at the proper place.
2. Now suppose if i don't have some characters in @Table2 , then it should display the existing character as it is.
Example :
INSERT INTO @Table1
SELECT 1,'Ritesh' UNION ALL
INSERT INTO @Table2
--SELECT 'R','X' UNION ALL
SELECT 'i', 'Y' UNION ALL
SELECT 't', 'Z' UNION ALL
SELECT 'B', 'A' UNION ALL
SELECT 'h', 'B' UNION ALL
SELECT 'a', 'C' UNION ALL
SELECT 'e', 'D' UNION ALL
SELECT 's', 'E'
so here i commented replacement of 'R' with 'X' so output should be "RYZDEB".
Please let me know how i can achieve the above two requirements.
Thanks,
Deepak
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply