Database
|
|
 |
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin
Submit an article or tip
Post your Blog
