|
|
Posted by Erland Sommarskog on 05/29/06 13:22
Zvonko (zvonko_NOSPAM_@velkat.net) writes:
>
> I was wondering if this is possible:
>
> To make things simpler, I have a table:
>
> CREATE TABLE example
> (text VARCHAR(30),
> number INTEGER);
>
> For example I have this values in it:
>
> someText 4
> someText1 6
> someText2 8
>
> Now I would like to get a ResultSet using only SQL in which I would
> like to have someText in four rows, someText2 in 6 rows and someText2 in
> 8 rows. Basically to get field text repeated for number of times.
CREATE TABLE example
(text VARCHAR(30),
number INTEGER);
insert example(text, number)
select 'someText', 4
union
select 'someText1', 6
union
select 'someText2', 8
go
SELECT e.text
FROM example e
JOIN numbers n ON n.n BETWEEN 1 AND e.number
Order by e.text
go
drop table example
numbers is a table that holds numbers frmo 1 and up. Here is a way to
set up:
-- Make sure it's empty.
TRUNCATE TABLE numbers
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
CREATE TABLE #numbers(n int IDENTITY PRIMARY KEY,
dummy int NULL)
INSERT #numbers (dummy)
SELECT TOP 1000000 o1.id
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 1000000 AND
(SELECT MIN(n) FROM #numbers) = 1 AND
(SELECT MAX(n) FROM #numbers) = 1000000
BEGIN
DECLARE @msg varchar(255)
-- Insert into the real table
INSERT numbers (n)
SELECT n FROM #numbers
SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) +
' rows into numbers'
PRINT @msg
END
ELSE
RAISERROR('#numbers is not contiguos from 1 to one million!', 16, -1)
go
DROP TABLE #numbers
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|