the actual query. the Com* columns added together are the key, I add a ',' and attach it to salnum which is the CSV value. If I put the full table path in to the subselect that shows COMS_PAYTABLE below, it errors:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'val'.
this makes sence because the 'val' column is an concatenation. If I replace the select * with select ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val, * I still get the same Invalid column name 'val'. Same if I take the entire select statement from above and put there.
select row_number() over (order by comgroup) as id,ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val from AWGCustom.dbo.COMS_PAYTABLE;
WITH CTE AS
(
SELECT *
FROM
AWGCustom.dbo.COMS_PAYTABLE
CROSS APPLY
(SELECT * FROM AWGCustom.dbo.DelimitedSplit8K(val, ',')) x
)
SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))
--into #temp
FROM CTE CTE1
INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id
WHERE CTE1.ItemNumber = 1;