sql - 如何反转像表一样的 GROUP BY？

Question

Animal Count  Color
------ -----  -----
Dog      2     brown
Cat      4     black

Result 

Animal  Color
------  
Dog     brown
Dog     brown
Cat     black
Cat     black
Cat     black
Cat     black

Answer 1

您可以通过 Common Table Expression 实现它在以下:

CREATE TABLE #Test
(
   Animal NVARCHAR(20),
   CountAnimals INT,
   Color NVARCHAR(20)
)

INSERT INTO #Test VALUES ('Dog', 2, 'brown'), ('Cat', 4, 'black');

WITH CTE AS (
    SELECT Animal,CountAnimals,Color FROM #Test

    UNION ALL 

    SELECT  Animal,CountAnimals-1,Color

    FROM CTE
    WHERE CountAnimals >= 2
)
SELECT Animal,Color
FROM CTE
ORDER BY Animal DESC
OPTION (MAXRECURSION 0);

DROP TABLE #Test

输出

Animal  Color
 Dog    brown
 Dog    brown
 Cat    black
 Cat    black
 Cat    black
 Cat    black

SQL FIDDLE