SQLで件数分SELECT結果を取得するには

質問

SQLSERVERのSQLで以下の【期待する結果】を取得する方法はありますか？

SELECT NAME,COUNT FROM CLASSROOM
【結果】
AAA,2
BBB,1
CCC,3

【期待する結果】
AAA
AAA
BBB
CCC
CCC
CCC

よろしくお願いします。

回答No.3

wingtodo · ベストアンサー

DECLARE @RESULT TABLE(
NAME varchar(10)
)

DECLARE NAMEs CURSOR FOR
SELECT NAME
FROM @CLASSROOM

DECLARE @NAME varchar(10)
DECLARE @iCount int
DECLARE @i int

OPEN NAMEs

FETCH NEXT FROM NAMEs INTO @NAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @i = 0
SELECT @iCount = [COUNT]
FROM @CLASSROOM
WHERE NAME = @NAME

WHILE @i < @iCount
BEGIN
INSERT INTO @RESULT(NAME)
VALUES(@NAME)
SET @i = @i + 1
END
FETCH NEXT FROM NAMEs INTO @NAME
END

CLOSE NAMEs
DEALLOCATE NAMEs

SELECT *
FROM @RESULT

こんなのどうでしょう。

回答No.2

t_ohta

SELECT NAME FROM CLASSROOM
じゃだめですか？

回答No.1

maiko0333

select name from classroom where count=1
union select name from classroom where count=2
union select name from classroom where count=2
union select name from classroom where count=3
union select name from classroom where count=3
union select name from classroom where count=3

くらいしか思いつきませんね。