Help with SQL Statement.
-
I'm sorry to bother but I can not find a solution for this problem:
I have 2 tables as follows:
Table 1:
NumberID Features
123456 1,2,3,4,5,6,7,8,9
123457 5,6,8,10,12
Table 2:
FeatureID Description
1 Description1
2 Description2
8 Description8
How can I link then so I can get the following results:
NumberID FeatureDescription
123456 Description1,Description2,Description3,Description4 etc.
OR:
NumberID FeatureDescription
123456 Description1
123456 Description2
Please help.
Thanks in advance.
-
hi there,
could you be more specific about the data. the data you have given is not sufficient to come up with a query that you need.
can you put some real data that you have (say probably 5 to 10 rows) in each table to get the appropriate answer?
-- PPS
-
OKay, guessing at the DDL, here's one possible way
CREATE TABLE #showme
(
NumberID INT
, Feature VARCHAR(20)
)
INSERT INTO #showme
SELECT 123456,'1,2,3,4,5,6,7,8,9'
UNION ALL
SELECT 123457,'5,6,8,10,12'
CREATE TABLE #showme2
(
FeatureID INT
, [Description] VARCHAR(30)
)
INSERT INTO #showme2
SELECT 1,'Description1'
UNION ALL
SELECT 2,'Description3'
SELECT
x.MyNbr
, #showme2.[Description]
FROM
#showme2
JOIN
(SELECT
RIGHT(LEFT(Feature,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+Feature,Number-1)))) MyNbr
FROM
master..spt_values, #showme
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(Feature)+1
AND
(SUBSTRING(Feature,Number,1) = ',' OR SUBSTRING(Feature,Number,1) = '')) x
ON x.MyNbr = #showme2.FeatureID
DROP TABLE #showme, #showme2
MyNbr Description
-------------------- ------------------------------
1 Description1
2 Description3
(2 row(s) affected)
I hope, you're doing this to get rid of 1NF violation in your structure.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here is some data per your request:
Table1:
number features
---------- ---------------------------------------------
111670.0 1|2|3|4|5|6|7|67|74|76|80|84|88|92|106
113650.0 1|13|21|29|30|31|42|43|45|49|55|67|74|84|87
115354.0 12|15|23|31|34|39|49|54|67|71|76|84|88|92|106
116772.0 11|31|34|41|43|44|49|52|53|71|76|92|96|97|98
117756.0 3|15|21|31|34|49|63|73|77|84|88|92|100|102
118294.0 29|74|76|83|87|92|106|128|133|138
Table2:
FeatureCode FeatureDesc
----------- -------------
1 Attached
2 Carport
3 Detached
4 Direct Entry
5 Heated
6 Underground
7 Tandem
8 Other-See Rmrks
9 Cabin
The goal is to replace the features column on table1 with the key in the table2, the result should look something like:
number features
---------- ---------------------------------------------
111670.0 Attached
111670.0 Carport
111670.0 Detached
111670.0 Direct Entry
Or something like:
number features
---------- ---------------------------------------------
111670.0 Attached, Carport, Detached, Direct Entry
I hope this explains my problem better.
Thanks in advance for your help.
-
Frank,
Thanks for your input, I tried but it did not work because the tables have different columns so I can not use the Union All.
-
You have seem to misunderstood me. The UNION ALL I only use to fill my sample table. The SELECT is the important thing for you.
CREATE TABLE #showme
(
NumberID INT
, Feature VARCHAR(100)
)
INSERT INTO #showme
SELECT 111670,'1|2|3|4|5|6|7|67|74|76|80|84|88|92|106'
CREATE TABLE #showme2
(
FeatureID INT
, [Description] VARCHAR(30)
)
INSERT INTO #showme2
SELECT 1,'Attached'
UNION ALL
SELECT 2,'Carport'
UNION ALL
SELECT 3,'Detached'
UNION ALL
SELECT 3,'Direct Entry'
SELECT
x.nID
, x.MyNbr
, #showme2.[Description]
FROM
#showme2
JOIN
(SELECT
RIGHT(LEFT(Feature,Number-1)
, CHARINDEX('|',REVERSE(LEFT('|'+Feature,Number-1)))) MyNbr
, #showme.NumberID nID
FROM
master..spt_values, #showme
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(Feature)+1
AND
(SUBSTRING(Feature,Number,1) = '|' OR SUBSTRING(Feature,Number,1) = '')) x
ON x.MyNbr = #showme2.FeatureID
DROP TABLE #showme, #showme2
nID Description
----------- ------------------------------
111670 Attached
111670 Carport
111670 Detached
111670 Direct Entry
(4 row(s) affected)
You have to customize the table names to suit your needs. And in case you stick with this really bad design, you might want to consider using your own numbers table instead of SQL Server's internal helper table master..spt_values.
Here's another interesting link for you:
http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
It work great, thanks a lot.
Unfortunatelly I have to use that data as it is not my design, is data that comes from someone else, poor design, I know but I'm stuck with it.
The problem is that I receive this data everyday and will have to run this query daily to insert the results in another table a lot easier to work with.
Thanks again my friend.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply