sql-server - 添加空白行以分隔2组T-Sql

我有一个查询结果:

#   unique   code   name
1   A1       ABC1   shirt
2   A1       ABC2   hat
3   A1       ABC3   shoes
4   A2       BDC2   hat

我要显示的结果是:

#   unique   code   name
1   A1       ABC1   shirt
    A1       ABC2   hat
    A1       ABC3   shoes
(blank) (blank) (blank) (blank)
2   A2       BDC2   hat

我如何在T-SQL中做到这一点？

最佳答案

如果您确实需要使用SQL进行此操作，请执行以下操作:

CREATE TABLE #t
(
   [#]      NVARCHAR(40),
   [unique] NVARCHAR(40),
   [code]   NVARCHAR(40),
   [name]   NVARCHAR(40)
)
INSERT INTO #t VALUES
   (1,'A1', 'ABC1', 'shirt'),
   (2,'A1', 'ABC2', 'hat'),
   (3,'A1', 'ABC3', 'shoes'),
   (4,'A2', 'BDC2', 'hat')

;WITH cte AS
(
    SELECT t1.[#], t1.[unique], t1.[code], t1.[name], t2.[#] AS [#2], t2.[unique] AS [unique2], t2.[code] AS [code2], t2.[name] AS [name2],
    ROW_NUMBER() OVER(PARTITION BY t1.[unique] ORDER BY t1.#) AS [rn]
    FROM #t t1
    LEFT JOIN #t t2 ON t1.[#] = t2.[#] + 1
), cte2 AS
(
SELECT [unique],[code],[name], REPLACE(CASE WHEN [unique] <> [unique2] THEN [rn]+1 ELSE CASE WHEN [rn] > 1 THEN '' ELSE rn END END,0,'') rn2
FROM cte
GROUP BY
GROUPING SETS
(
[unique],
([unique],[unique2],rn),
([unique],[unique2],[code],rn),
([unique],[unique2],[code], [name],rn)
)
)
SELECT rn2 AS [#], CASE WHEN rn2 IS NULL THEN NULL ELSE [unique] END [unique], [code], [name]
FROM cte2
WHERE [code] IS NOT NULL AND [name] IS NOT NULL OR rn2 IS NULL

DROP TABLE #t

输出

#       unique  code    name
1       A1      ABC1    shirt
        A1      ABC2    hat
        A1      ABC3    shoes
NULL    NULL    NULL    NULL
2       A2      BDC2    hat
NULL    NULL    NULL    NULL