Tough split issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tough split issue

Author

Topic

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-11 : 13:30:50

Hi,

I've split my head trying to figure out how to do this, but because I'm not a SQL expert, I thought I'd ask a SQL expert. So, here we go.

I have a table (strings) that creates a concatinted value of record ID's. This table has two key columns, ReferenceID and Value. The value also references record ID's.

EXAMPLE:
RefID Value
12 15,18,20,25

In another table (integers), I have the same structure, but without the concatination:

RefID Value
15 18.0
18 9.3
20 2.5
25 8.0

What I need to do is get all the values from integers for the values from strings, so my end result will be :

RefID Value
12 18.0,9.3,2.5,8.0

or, (even better yet):
RefID Value Value Value Vlaue
12 18.0 9.3 2.5 8.0

There is no set number of values in the first example. There could be only one value or 15 comma separated values.

This merely needs to be a select and not an insert or update.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:40:10

Not sure why your table is designed like this. Have you heard of 1st Normal form?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:41:43

Are you using SQL 2005?

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-11 : 13:46:32

I am using SQL 2005.

The table design is weird, I know that. Unfortunately, I was not the one that designed it, and redesigning it is not an option.

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-11 : 13:48:02

Better understanding of the table design. In integers, there is another column, FieldID. The refID represents the record number, and the FieldID represents the field. in this table, there will be only one ref number per fieldID and value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 14:02:27

Create a UDF like this

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val Numeric(10,1) 
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

Now use it like this

;With Your_CTE (RefID,Seq,Val) AS
(
SELECT t.RefID,t.ID, i.Value  
FROM
(
SELECT t1.RefID,b.ID,b.Val
FROM strings t1
CROSS APPLY dbo.ParseValues(t.Value)b
)t
INNER JOIN Integers i
ON i.RefID=t.Val
)

SELECT c1.RefID,
STUFF(SELECT c2.Value + ',' AS [text()] 
FROM Your_CTE c2 
WHERE c2.RefID=c1.RefID
ORDER BY c2.Seq
FOR XML PATH('')),1,1,'') AS Value
FROM Your_CTE c1

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-12 : 15:10:55

When I run the second part, I get an incorrect syntax at CROSS.

I guess I should be more specific in the table structures:
In StringValues, I have FK_ReferenceID, FK_FieldID, FieldProperty and Value
In IntegerValues I have the same structure

So, in the stringvalues, the Value will represent a comma separated value of FK_ReferenceID's from the IntegerValues.
What I need to do is pull all the values from the IntegerValues table for a specific FK_FieldID where the FK_ReferenceID is in the Value of the Stringvalues table.

I'm not concerned at all for the FieldProperty.

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-12 : 15:20:25

I should note that I did change all the references of RefID to FK_ReferenceID in the above script.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 16:22:31

[code]-- Prepare sample data
DECLARE @Source TABLE
(
RefID INT,
Records VARCHAR(1000)
)

INSERT @Source
SELECT 12, '15,18,20,25'

DECLARE @Lookup TABLE
(
RefID INT,
Val SMALLMONEY
)

INSERT @Lookup
SELECT 15, 18.0 UNION ALL
SELECT 18, 9.3 UNION ALL
SELECT 20, 2.5 UNION ALL
SELECT 25, 8.0

-- Show the expected output
SELECT s.RefID,
s.Records AS Original,
SUBSTRING(f.v, 2, 8000) AS [Replacement]
FROM @Source AS s
CROSS APPLY (
SELECT ',' + LTRIM(STR(l.Val, 15, 1))
FROM @Lookup AS l
WHERE ',' + s.Records + ',' LIKE '%,' + CAST(l.RefID AS VARCHAR(12)) + ',%'
FOR XML PATH('')
) AS f(v)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources