Inserting rows dynamiccaly on based on calculation

vkap

I have a db with following values : col1 col2 col3 position aaa rack1 1-3 3 bbb rack2 22-23 2 ccc rack3 19-20 2 Output should be col1 col2 col3 position aaa rack1 1-3 1 aaa rack1 1-3 2 aaa rack1 1-3 3 bbb rack2 22-23 22 bbb rack2 22-23 23 ccc rack3 19-20 19 ccc rack3 19-20 20 Basically, its taking the position and creating a row for each value from col3. Can i do this on-fly and create a temp table? or i have to create a procedure? thanks!!! VK

Mycroft Holmes

If you are doing this in the database you are going to have to write some code, you can't magic it into the multiple row. I would look at the method of inserting the original record and change or enhance that method. Alternatively you can use an insert trigger spit, I would do this as a last resort.

Never underestimate the power of human stupidity RAH

__TR__

Try this

CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)

CREATE TABLE #Temp2
(
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)

INSERT INTO #Temp
SELECT 'aaa', 'rack1', '1-3', '3' UNION
SELECT 'bbb', 'rack2', '22-23', '2' UNION
SELECT 'ccc', 'rack3', '19-20', '2'

DECLARE @TotalRowCount INT
DECLARE @RowCount INT
DECLARE @Positon INT
DECLARE @I INT

SET @RowCount = 1

SELECT @TotalRowCount = COUNT(*) FROM #Temp

WHILE @RowCount <= @TotalRowCount
BEGIN

SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount

SET @I = 1

WHILE @I <= @Positon
BEGIN

	INSERT INTO #Temp2
	SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp  WHERE Id = @RowCount
	
	SET @I = @I + 1
END

SET @RowCount = @RowCount + 1

END

SELECT * FROM #Temp2

DROP TABLE #Temp
DROP TABLE #Temp2