SQLTeam.com | Weblogs | Forums

How to join these tables?


#1

I have a series of different articles. Each article is described by a number of features. Articles that belong to the same family always have the same set of features. To each family a template is assigned. This following table reflects that relationship. 'X' means that in the article's description that particular feature is used (with it's value)!

Template   Length    Width   Thread    Form    Diameter    Process    Thickness    Transition
----------------------------------------------------------------------------------------------------------------------------
TemplateA     X        X                                       X           X   
TemplateB              X         X        X                                              X
TemplateC     X                  X                  X                      X

The article description is a text column that lists up these features using a comma delimiter:
Description = 'Width: 20 mm, Thread: metric, Form: conic, Transition: step'

I can't find an expression that returns the correct template for a given Description. In this case: TemplateB

I transformed the description into a table using dynamic SQL, that has the column names Width, Thread, Form and Transition but I can't see to join these tables in order to get the template name.

Any hints?
Martin


#2

Would you please post the code you have so far?


#3

I doubt that posting the code I got so far will help. It's rather difficult to read as everything is wrapped in dynamic SQL. Keep in mind that it returns for an article ART001 a Table with one single row like Table [Sample] =

ITEMID   WIDTH    THREAD    FORM    TRANSITION
-----------------------------------------------
ART001   20 mm    metric    conic    step

this has to be joined with the Table [Templates] - see above - in order that in some way it returns the correct template. The [Templates] table had been priory created and manually filled and contains all possible features as their columnnames.

CREATE TABLE #FeatureItems
(
ITEMID	nvarchar(20),
FI_Key		varchar(700),
FI_Key2		int,
FI_Value	varchar(700),
PRIMARY KEY
(
	ITEMID,
	FI_Key,
	FI_Key2
)
)

;with Source as
(Select ITEMID, Text_T 
 FROM WK_ITEMTEXT					  
 WHERE ITEMID = @ITEMID)

INSERT INTO #FeatureItems
(
ITEMID,
FI_Key,
FI_Value,
FI_Key2
)
SELECT	ITEMID,
T_Key,
T_Value,
T_Key2
FROM Source
CROSS APPLY
(
	SELECT	T_Offset = CHARINDEX(':', Item, 0), ItemNumber, Item
	FROM dbo.Split(Text_T, '<br />')
) AS Y
CROSS APPLY
(
	SELECT	T_Key = LTRIM(CASE
				WHEN T_Offset = 0 and LEFT(Item, 11) = 'Certificado' THEN 'Certificado'
				WHEN T_Offset = 0 and ItemNumber = 1 THEN 'Clase' 
				WHEN T_Offset = 0 THEN 'Categoria' 
				ELSE LEFT(Item, T_Offset - 1)
				END),
		T_Value = LTRIM(RIGHT(Item, LEN(Item) - T_Offset)),
		T_Key2 = ItemNumber
) AS Z


Declare @features varchar(max) = ''

select @features += QUOTENAME(FI_Key) +','
from #FeatureItems
GROUP BY FI_Key
ORDER BY avg(FI_Key2) asc


set @features = left (@features, len(@features)-1)
print @features

declare @para1 nvarchar(5)
set @para1 = ';'


declare @sql nvarchar(max)
set @sql = 
'SELECT * from (SELECT ITEMID, FI_Key, STUFF(
     (SELECT ''' + @para1 +''' + FI_Value
      FROM  #FeatureItems as x
      WHERE x.FI_Key = #FeatureItems.FI_Key and x.ITEMID = #FeatureItems.ITEMID
      FOR XML PATH (''''))
      , 1, 1, '''')  AS FI_Value
from #FeatureItems
GROUP By ITEMID, FI_Key)a

PIVOT (min(FI_Value) for FI_Key in (' + @features + ')) as pvt'
exec sp_executesql @sql

DROP TABLE #FeatureItems

#4

That's not so bad. Now, if you can post:

  1. CREATE TABLE for tables wk_items and Templates
  2. INSERT INTO statements to populate wk_items and Templates

Then I can run it on my own and see what's up


#5

Gbritton, I made this example to make things easier, because in real life there are up to 350 different features. I rather send you the code to rebuild my small example, beside the fact that I haven't set up the Templates table yet in the way I described above, and I started to doubt that this is the smartest way to implement the rule that assigns a template to a set of features.

I actually came up right now with something that seems to work for me by restructuring the template table as an EAV table and by inverting columns and rows. The sample Table is what is returned by the posted dynamic SQL code, just without applying the Pivot function.

create table #templates(feature nvarchar(20),Template nvarchar(1))
create table #sample(feature nvarchar(20),value nvarchar(300))

insert into #templates values ('Length','A')
insert into #templates values ('Length','C')
insert into #templates values ('Width','A')
insert into #templates values ('Width','B')
insert into #templates values ('Thread','B')
insert into #templates values ('Thread','C')
insert into #templates values ('Form','B')
insert into #templates values ('Diameter','C')
insert into #templates values ('Process','A')
insert into #templates values ('Thickness','A')
insert into #templates values ('Thickness','C')
insert into #templates values ('Transition','B')

insert into #sample values ('Width','20 mm')
insert into #sample values ('Thread','metric')
insert into #sample values ('Form','conic')
insert into #sample values ('Transition','step')

drop table #templates
drop table #sample

When I do:

Select a.feature as TF, a.Template, b.feature as SF, b.value from #templates a inner join #sample b on a.feature = b.feature

the query returns beside the 4 correct records for Template B some occasional matches with other templates. What makes Template B the correct result is the occurrence of Template B in both the [Template] and the [Sample] table. All features have to have the same occurrence. So I do:

;with CNT as (Select Template, COUNT(Template) as cnt from #templates Group by Template),
match as (Select a.feature as TF, a.Template, b.feature as SF, b.value from #templates a inner join #sample b on     a.feature = b.feature)

Select x.Template from (
Select Template, Count(Template) as cnt from match Group by Template) x
inner join CNT on x.Template = CNT.Template and x.cnt = CNT.cnt

This returns the correct template B. If you have a smoother way to do this, I'll be glad to know it.