I have an Item Table with text string column that contains information on how to build that item. There are 8000 items.
Example: "length: 150 mm, width: 65 mm, shape: conic" The features can be up to 150 and not every item is described with the same features.
I need to build a table with the columns: ItemID and all possible 150 features
-
Step: I build a table 'Features' with all possible features by splitting the text with delimiter ',' and removing the values so I get only 'length', 'width', 'shape'.
-
Step: I build a pivot query with the item table and the features table that returns:
Item Length Width Shape ....
TW24 150 65 conic ....
The problem I'm having is that the pivot table returns just one line for each item I do the split for and I can't imagine how to loop through all items.
declare @Categories nvarchar(max) = ''
declare @sql nvarchar(max)
declare @testString varchar(max)
set @testString = (SELECT 'ITEMID:'+ItemID+'<br />'+Text_T from (
select WK_ITEMTEXT.ITEMID, Text_T, row_number() over(order by TEXT_T) as ord
FROM WK_ITEMTEXT
left outer join Inventtable a on WK_ITEMTEXT.ITEMID = a.itemid and a.dataareaid = 'ES75'
left outer joinDOCUREF b on a.RECID = b.REFRECID AND b.ACTUALCOMPANYID = 'ES75' and b.REFTABLEID = 175 and b.name not like '%Cert%'
left outer join DOCUVALUE c on b.VALUERECID = c.RECID
left outer join DOCUPARAMETERS d on d.DATAAREAID = 'ES75'
WHERE left(a.itemname,2) ='TW' and b.Name = 'Plano' and Text_T is NOT NULL)a
where ord = 1)
;with query as (
SELECT Tag, Value from (SELECT Item, CASE WHEN CHARINDEX(':', Item, 0) = 0 and left(item,11) = 'Certificado' THEN 'Certificado'
WHEN CHARINDEX(':', Item, 0) = 0 THEN 'Categoria' ELSE left(item, CHARINDEX(':', Item, 0)) END as Tag, LTRIM(right(item, len(item) -CHARINDEX(':', Item, 0))) as Value
FROM dbo.Split(@testString, '<br />'))a)
select @Categories += QUOTENAME(Tag) +',' from Model_tags
set @Categories = left (@Categories, len(@Categories)-1)
declare @param1 nvarchar(20)
declare @param2 nvarchar(20)
declare @param3 nvarchar(20)
declare @param4 nvarchar(20)
declare @param5 nvarchar(20)
declare @param6 nvarchar(20)
declare @param7 nvarchar(20)
declare @param8 nvarchar(20)
declare @param9 nvarchar(20)
set @param1 = 'ES75'
set @param2 = '%Cert%'
set @param3 = 'Plano'
set @param4 = 'TW'
set @param5 = 'Certificado'
set @param6 = 'Categoria'
set @param7 = '<br />'
set @param8 = ':'
set @param9 = 'ItemID:'
set @sql =
'declare @testString varchar(max)
set @testString = (SELECT ''' + @param9 + '''+ ItemID +''' + @param7 + '''+Text_T from (
select WK_ITEMTEXT.ITEMID, Text_T, row_number() over(order by TEXT_T) as ord
FROM WK_ITEMTEXT
left outer join Inventtable a on WK_ITEMTEXT.ITEMID = a.itemid and a.dataareaid = ''' + @param1 + '''
left outer join DOCUREF b on a.RECID = b.REFRECID AND b.ACTUALCOMPANYID = ''' + @param1 + ''' and b.REFTABLEID = 175 and b.name not like ''' + @param2 + '''
left outer join DOCUVALUE c on b.VALUERECID = c.RECID
left outer join DOCUPARAMETERS d on d.DATAAREAID = ''' + @param1 + '''
WHERE left(a.itemname,2) =''' + @param4 + ''' and b.Name = ''' + @param3 + ''' and Text_T is NOT NULL)a
where ord = 1)
;with query as (
SELECT Tag, Value from (SELECT Item, CASE WHEN CHARINDEX(''' + @param8 + ''', Item, 0) = 0 and left(item,11) = ''' + @param5 + ''' THEN ''' + @param5 + '''
WHEN CHARINDEX(''' + @param8 + ''', item, 0) = 0 THEN ''' + @param6 + ''' ELSE left(item, CHARINDEX(''' + @param8 + ''', item, 0)) END as Tag, LTRIM(right(item, len(item) -CHARINDEX(''' + @param8 + ''', item, 0))) as Value
FROM dbo.Split(@testString, ''' + @param7 + '''))a)
SELECT * from(
SELECT CASE WHEN Tag = ''' + @param9 + ''' THEN ''' + @param9 + ''' ELSE Tag END as Tag, CASE WHEN Tag = ''' + @param9 + ''' THEN Value ELSE VALUE END as Value from query)a
PIVOT (Min(Value) for Tag in (' + @Categories +')) as pvt'
exec sp_executesql @sql
I would have a Key / Value table with the individual pieces of the Features and then reassemble them (e.g. as a comma or <br /> delimited list, using a VIEW for those reports that want them in that format. There is a nice way to get a delimited list (i.e. from multiple rows of a Feature Items table) in SQL using an XML trick
The code that I posted executes in 1 second and gives me the desired result and format and speed, but just for ONE single Item, while I need to loop through all Items. I inserted a simple loop function, but that works just in the first loop. Entering the second loop and after the row had been successfully saved to the 
part. )