Geek Answers Handbook

How to change the GROUP BY table as?

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

Result 

Animal  Color
------  
Dog     brown
Dog     brown
Cat     black
Cat     black
Cat     black
Cat     black
+4
source share
5 answers

You can achieve It with the help of Common Table Expressionthe 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

+5
source

You need to enter an artificial table (or view) the_row_holder_table with the number count count> = max 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_row_holder_table has only one column rowwith values

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

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

+2
source 
declare @ints table(ID int)
declare @animals table(animal varchar(20),[count] int,color varchar(20))
insert into @animals values ('Dog',      2     ,'brown'),('Cat',      4     ,'black')

declare @int int = 1
declare @maxInt int = (SELECT MAX([count]) from @animals)
while @int <= @maxInt 
BEGIN
    INSERT INTO 
        @ints
    VALUES
        (@int)

    SET @int = @int + 1
END


SELECT
    animal,
    color
FROM
    @animals a
    INNER JOIN @ints i ON i.ID <=a.[count]
ORDER BY
    animal ASC
0

- :

:

DECLARE @tbl TABLE(Animal varchar(100), Count INT, Color VARCHAR(100))
INSERT INTO @tbl
VALUES
    ('Dog',2,'brown'),
    ('Cat',4,'black')

cte + cross. :

DECLARE @max INT=(SELECT MAX(Count) FROM @tbl);
;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < @max )
SELECT 
    t.Animal,
    t.Count,
    t.Color 
FROM 
    @tbl as t 
    CROSS APPLY 
    (
        SELECT * FROM Nbrs WHERE Nbrs.n<=t.Count
    ) AS f
ORDER BY t.Animal DESC


Dog 2   brown
Dog 2   brown
Cat 4   black
Cat 4   black
Cat 4   black
Cat 4   black
0
WITH CTE AS (

SELECT animal,count,color FROM tes

UNION ALL 

SELECT  animal,count-1,color

FROM CTE
WHERE count > 1
 )

SELECT animal,color
FROM CTE
group by animal,color
-1

More articles:


All Articles