February 16, 2011 at 12:27 am
I have a configuration table with data as such:
ID NAME
=======
1 ABC
2 XYZ
3 DEF
4 GHI
5 UVW
And a main table which is using the above, in column3 i refer the above ID as CSV (comma separated values)
IDColumn1Column2Column3
=======================
11,2,3
23,5,2
Now when I query the Main table I need to get the NAME of the configuration table as this
If I select ID, Column3 from Main table then
the result should be
1, 'ABC,XYZ,DEF'
2, 'DEF,UVW,XYZ'
how to go about it
Stored Procedure ?
In query itself ?
UDF ?
February 16, 2011 at 1:23 am
I would reconsider the database design. Storing multiple values comma separated in one column will always give you complexities and goes against one of the basic principles of database design. (Have a read up on normalisation if that is not familiar to you).
Far simpler is to add another table that contains simply the unique identifier of your "main" table and the other, then if there is a relationship betwen the main table and the other that uses 3 of the values, you have 3 rows in the associative table.
Add appropriate constraints to the associative table to prevent dups and the language will start to work for you rather than against you.
Mike
February 16, 2011 at 2:23 am
First, let us set-up the sample data:
DECLARE @Configuration TABLE
(
ID INT,
NAME VARCHAR(3)
)
INSERT INTO @Configuration
SELECT 1, 'ABC'
UNION ALL SELECT 2, 'XYZ'
UNION ALL SELECT 3, 'DEF'
UNION ALL SELECT 4, 'GHI'
UNION ALL SELECT 5, 'UVW'
DECLARE @Main TABLE
(
ID INT,
Column3 VARCHAR(15)
)
INSERT INTO @Main
SELECT 1, '1,2,3'
UNION ALL SELECT 2, '3,5,2'
Had u provided the above, it would have saved a lot of time for me (for others as well :w00t:)
Now, the code that you need; it first splits the CSV, JOINs with the configuration to get the names, APPENDs each NAME w.r.t the IDs
;WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,
SplitValues AS
(
SELECT Main.ID, CrsApp.ItemNumber, Config.NAME
FROM @Main Main
CROSS APPLY
(
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(Main.Column3, N, CHARINDEX(',', Main.Column3 + ',', N) - N) AS Item
FROM cteTally
WHERE N < LEN(Main.Column3) + 2
AND SUBSTRING(',' + Main.Column3, N, 1) = ','
) CrsApp
JOIN @Configuration Config
ON Config.ID = CrsApp.Item
)
SELECT Split_Outer.ID,
STUFF((SELECT ',' + Split_Inner.NAME
FROM SplitValues Split_Inner
WHERE Split_Inner.ID = Split_Outer.ID
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') Column3
FROM SplitValues Split_Outer
GROUP BY Split_Outer.ID
Please note that i have shamelessly copied Mr.Jeff Moden's dbo.DelimitedSplit8K in the code. I reckon this is not the latest version of the split function :doze: , but if Jeff bumps on this thread, he may provide us with his latest one.
But as Mike John pointed out, u could very well save the CSV in the Main as a separate column with values already split. That will save us a lot of work
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply