There are two tables.
One table contains:
Name value
A 1
B 2
C 3
D 4
another table contains
City value
aa 1
bb 2,3
cc 3
dd 1,2,4
I want an output which contains:
City value Name
aa 1 A
bb 2,3 B,C
cc 3 C
dd 1,2,4 A,B,D
How can i do this using cursor?
Thanks. Your question really made me appreciate normal forms.
Anyhow, I am going to go out on a limb and assume you asked for a cursor-based solution because you assumed the non-normalized data could not be handled.
Once you have the function to materialize the rows into a value list, you can solve this with a simple query.
Given:
CREATE TABLE dbo.NV (Name CHAR(1), Value INT)
CREATE TABLE dbo.CV (City varchar(88), ValueList VARCHAR(88))
loaded with the data you indicated.
And this SQL script:
GO
CREATE FUNCTION dbo.f_NVList(@VList VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @VAL VARCHAR(928)='',
@FIDescr VARCHAR(55)
SELECT @VAL = COALESCE(@VAL + LTRIM(map.name),'') + ','
FROM dbo.nv Map
WHERE CHARINDEX(','+LTRIM(STR(map.value)) + ',', ','+@VList + ',' ) > 0
SET @VAL = SUBSTRING(@VAL,1,len(@VAL)-1)
RETURN(@VAL)
END
GO -- end of function
-- this generates the output, using the function to materialize the name-values
SELECT cv.* , dbo.f_NVList(cv.ValueList ) as NameList FROM dbo.CV cv;
producing your output:
PLEASE DON'T - but If you really need the cursor for some reason, instead of
SELECT cv.* , dbo.f_NVList(cv.ValueList ) as NameList FROM dbo.CV cv;
use this
OPEN BadIdea;
FETCH NEXT FROM BadIdea INTO @C, @VList
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NameList = dbo.f_NVList(@Vlist)
INSERT INTO @OUT VALUES( @C, @VLIST , @NameList )
FETCH NEXT FROM BadIdea INTO @C, @VList
END
CLOSE BadIdea
DEALLOCATE BadIdea
select * from @OUT ;
Please give a try on this:
;with nv as (
select *
from (values ('A', '1'), ('B', '2'), ('C', '3'), ('D', '4')) a (Name, value))
, cv as (
select *
from (values ('aa', '1'), ('bb', '2,3'), ('cc', '3'), ('dd', '1,2,4')) a(City, value)
)
, cv2 as (
select cv.City
, case when charindex(',',cv.value)>0 then LEFT(cv.value, charindex(',',cv.value)-1) else cv.value end value
, case when charindex(',',cv.value)>0 then right(cv.value, LEN(cv.value)-len(LEFT(cv.value, charindex(',',cv.value)-1)+',')) end leftover
from cv
union all
select cv.City
, case when charindex(',',cv.leftover)>0 then LEFT(cv.leftover, charindex(',',cv.leftover)-1) else cv.leftover end value
, case when charindex(',',cv.leftover)>0 then right(cv.leftover, LEN(cv.leftover)-len(LEFT(cv.leftover, charindex(',',cv.leftover)-1)+',')) end leftover
from cv2 cv
where cv.leftover is not null
)
select *
, stuff((
select ','+nv.Name
from cv2
join nv on nv.value=cv2.value
where cv2.City=cv.City
for xml path('')
), 1, 1, '') Name
from cv
With cv2 I split the values to City, with a recursive CTE. After that I calculate the new Name for each City. I don't know how fast is on a big table, but I think it is better then cursor.
using CROSS APPLY we will initially delimit all the values and then we can acheieve using XML path () and CTE's
DECLARE @Name table (name varchar(5),value int)
INSERT INTO @Name (name,value)values ('A',1),('B',2),('C',3),('D',4)
DECLARE @City table (city varchar(10),value varchar(10))
INSERT INTO @City (city,value)values ('aa','1'),('bb','2,3'),('cc','3'),('dd','1,2,4')
Code :
;with CTE AS (
SELECT A.city,
Split.a.value('.', 'VARCHAR(100)') AS Data
FROM
(
SELECT city,
CAST ('<M>' + REPLACE(value, ',', '</M><M>') + '</M>' AS XML) AS Data
FROM @City
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
),CTE2 AS (
Select c.city,t.value,STUFF((SELECT ', ' + CAST(name AS VARCHAR(10)) [text()]
FROM @Name
WHERE value = c.Data
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
from CTE C
INNER JOIN @Name t
ON c.Data = t.value
)
select DISTINCT c.city,STUFF((SELECT ', ' + CAST(value AS VARCHAR(10)) [text()]
FROM CTE2
WHERE city = C.city
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') As Value ,STUFF((SELECT ', ' + CAST(List_Output AS VARCHAR(10)) [text()]
FROM CTE2
WHERE city = C.city
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ')As Name from CTE2 C
First, you need a function to split your comma-delimited values. Here is the DelimitedSplit8K written by Jeff Moden and improved by the community. This is regarded as one of the fastest SQL-based string splitter.
You should also read on FOR XML PATH(''), a method to concatenate strings. Check this article by Aaron Bertrand for more information.
SELECT
*
FROM Table2 t2
CROSS APPLY(
SELECT STUFF((
SELECT ',' + Name
FROM Table1
WHERE Value IN(
SELECT CAST(s.Item AS INT) FROM dbo.DelimitedSplit8K(t2.Value, ',') s
)
FOR XML PATH(''), type).value('.', 'VARCHAR(MAX)'
), 1, 1, '')
)x(Name)
Notes:
DelimitedSplit8K.