CREATE TABLE t1
(
    id INT,
    style VARCHAR(10),
    code INT,
    name VARCHAR(50),
    pid INT
)
INSERT INTO t1
SELECT 1,'大类',1,'水泥',NULL UNION ALL
SELECT 2,'小类',1,'速凝水泥',1 UNION ALL
SELECT 3,'小类',2,'慢凝水泥',1 UNION ALL
SELECT 4,'品牌',1,'feifei',2 UNION ALL
SELECT 5,'品牌',2,'nini',2
SELECT * FROM t1

SELECT a.code AS [大类code],a.NAME AS [大类name],b.code AS [小类code],b.NAME AS [小类name],c.code AS [品牌code],
c.NAME AS [品牌name]
FROM t1 AS a WITH(NOLOCK) INNER JOIN t1 AS b ON b.pid=a.code AND a.pid IS NULL AND b.code=2 INNER JOIN
t1 AS c WITH(NOLOCK) ON c.pid=b.code AND c.style='品牌'

-------------------------------
大类code    大类name    小类code    小类name    品牌code    品牌name
1    水泥    2    慢凝水泥    1    feifei
1    水泥    2    慢凝水泥    2    nini