The first column is just an auto incement number "Row1" and "Row2" is auto increment of the subcategories of the current category
like tree view:
1-
|- 1.1
|- 1.2
2-
|- 2.1
|- 2.2
the table is short by category name
Here is my code
SELECT
DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr
ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
GetDate() AS DDate, CASE WHEN T1.ID IN
(SELECT ID
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) + RIGHT(T1.ID, 4) IN
(SELECT DISTINCT ParentID + ParentID
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) IN
(SELECT DISTINCT LEFT(ID, 4)
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 0 ELSE 0 END AS publish
FROM clroot.Ctgry1 LEFT JOIN
clroot.Ctgry1 AS T1 ON T1.ParentID = clroot.Ctgry1.ID
WHERE (T1.Descr IS NOT NULL)