Esto podría servirte:
Código sql:
Ver originalDECLARE @TABLE TABLE( idcat INT, nomcat VARCHAR(10), idcatpadre INT)
INSERT INTO @TABLE VALUES( 1, 'ALFA', NULL )
INSERT INTO @TABLE VALUES( 2, 'Cat 1', 1 )
INSERT INTO @TABLE VALUES( 3, 'Cat 2', 1 )
INSERT INTO @TABLE VALUES( 4, 'BETA', NULL )
INSERT INTO @TABLE VALUES( 5, 'Cat 3', 4 )
INSERT INTO @TABLE VALUES( 6, 'Cat 4', 4 )
INSERT INTO @TABLE VALUES( 7, 'Cat 5', 1 )
INSERT INTO @TABLE VALUES( 8, 'Cat 6', 9)
INSERT INTO @TABLE VALUES( 9, 'GAMMA', NULL )
SELECT idcat, nomcat, idcatpadre, idcol
FROM(
SELECT idcat, nomcat, idcatpadre = idcat, idcol = 1
FROM @TABLE
WHERE idcatpadre IS NULL
UNION
SELECT idcat, nomcat, idcatpadre, idcol = 2
FROM @TABLE
WHERE idcatpadre IS NOT NULL
) X
ORDER BY idcatpadre, idcol
Saludos.