How to reverse a GROUP BY like table?

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

You can achieve It with Common Table Expression in following:

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

OUTPUT

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

SQL FIDDLE

Answer 2

You need to introduce an artificial table (or view) the_row_holder_table with row count>=max count in the initial table. Then just

select gr.Animal, gr.Color
from grouped gr 
join the_row_holder_table on gr.count<the_row_holder_table.row

UPDATE: Suppose the the_row_holder_table has just one column row with values

row
-----
1
2
3
...

Then each row of the grouped table is connected (by gr.countcount rows of the the_row_holder_table table