CURSOR FOR (Remove Comma, Create New Column)
-
I have a field that has data that is separated by comma's. The data in the field needs to then be added as separate fields. (See Red)
Example: Field1, Field2, Field3, Field4 with values in @ResponseList
I am trying to use the Cursor to create a new field value but not having luck...any suggestions?
--Remove Comma
BEGIN
SET NOCOUNT ON
DECLARE @ResponseList varchar(500)
SET @ResponseList ='Value1, Value2, Value3, Value4'
if not exists (select * from sysobjects where name='#ResponseList')
CREATE TABLE #ResponseList(
Reason varchar(150))
DECLARE @Response varchar(150), @Pos int
SET @ResponseList = LTRIM(RTRIM(@ResponseList))+ ','
SET @Pos = CHARINDEX(',', @ResponseList, 1)
IF REPLACE(@ResponseList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Response = LTRIM(RTRIM(LEFT(@ResponseList, @Pos - 1)))
IF @Response <> ''
BEGIN
INSERT INTO #ResponseList (Reason) VALUES (CAST(@Response AS varchar))
END
SET @ResponseList = RIGHT(@ResponseList, LEN(@ResponseList) - @Pos)
SET @Pos = CHARINDEX(',', @ResponseList, 1)
END
END
Select * from #ResponseList
END
Drop TABLE #ResponseList
--CURSOR
DECLARE @mycur CURSOR
DECLARE @test-2 VARCHAR(255)
SET @mycur = CURSOR FOR
SELECT Reason FROM #ResponseList
OPEN @mycur
FETCH NEXT FROM @mycur INTO @test-2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @test-2 as field1
FETCH NEXT FROM @mycur INTO @test-2
END
DEALLOCATE @mycur
--Select * from #ResponseList
-
If you want to use cursor for achieving this
try this
DECLARE
@STR VARCHAR(100),
VARCHAR(10),
@L
INT
SET
@STR= '1,2,3,4,5,6,7,8,9,10,0'
DECLARE
@tab TABLE (VAL INT)
WHILE
DATALENGTH(@STR)>0
BEGIN
IF (DATALENGTH(@STR)=1 AND (@STR NOT LIKE '%[^0-9]%'))
BEGIN
INSERT INTO @tab VALUES (@STR)
BREAK
END
ELSE
BEGIN
SET @L=CHARINDEX(',',@STR)
SET @STR=RIGHT(@STR, LEN(@STR)-@L)
INSERT INTO @tab VALUES (@val)
END
END
SELECT
* FROM @tab
Otherwise , search this forum you can find so many examples without using cursors
-
I get the first part..But what are u trying to in the second part (using Cursor)? Can you explain what u need?
Thanks
Sreejith
-
1.) Field has many values that are separated by commas (Code for this above)
2.) The values then need to be Columns for the original query.
Current Fields within a view:
MyId | Reason
1 | value1, value2, value3
New Fields Needed :
MyId | Reason1 | Reason2 | Reason3
1 | value1 | value2 | value3
I created the below function to return table values so that I could join the two BUT get error
is not a recognized OPTIMIZER LOCK HINTS option
select *
from tbl a
join dbo.fnTest(a.MyId) b Where a.MyId=b.MyId
--Function
CREATE FUNCTION fnTest
(@MyId varchar(12))
RETURNS @ReasonTable TABLE
(
MyId varchar(12),
Response1 varchar(250),
Response2 varchar(250),
Response3 varchar(250),
Response4 varchar(250)
)
AS
BEGIN
-- Declare @MyId varchar(30)
-- Set @MyId = '1122336XXDD'
--SET NOCOUNT ON
DECLARE @ResponseList varchar(500)
SET @ResponseList = (SELECT reason from tbl where MyId = @MyId)
DECLARE @Response varchar(150), @Pos int
DECLARE @Response1 varchar(150), @Response2 varchar(150), @Response3 varchar(150), @Response4 varchar(150)
SET @ResponseList = LTRIM(RTRIM(@ResponseList))+ ','
SET @Pos = CHARINDEX(',', @ResponseList, 1)
IF REPLACE(@ResponseList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Response = LTRIM(RTRIM(LEFT(@ResponseList, @Pos - 1)))
IF @Response <> ''
BEGIN
IF (@Response1 IS NULL) BEGIN SET @Response1 = @Response END
ELSE IF (@Response2 IS NULL) BEGIN SET @Response2 = @Response END
ELSE IF (@Response3 IS NULL) BEGIN SET @Response3 = @Response END
ELSE IF (@Response4 IS NULL) BEGIN SET @Response4 = @Response END
END
SET @ResponseList = RIGHT(@ResponseList, LEN(@ResponseList) - @Pos)
SET @Pos = CHARINDEX(',', @ResponseList, 1)
END
END
Begin
Insert Into @ReasonTable
SELECT @MyId as MyId, @Response1 as Response1, @Response2 as Response2, @Response3 as Response3, @Response4 as Response4
Return
End
END
-
I am using a Multi-statement Table-Valued Function
I tryed this and i get an error when trying to use a value from table a
Line 2: Incorrect syntax near '.'.
Select * from tbl a
Left Join (Select * from dbo.fnTest(a.MyId)) as b
ON a.MyId = b.MyId
-
I found a post that you cannot do what I am trying in SQL 2000
Passing column names as parameter to a table-valued UDF is not allowed in SQL 2000.
But if you can wait
, it can be done in SQL 2005 by using CROSS APPLY operator as follows: -select c.* from myCustomerTable as c CROSS APPLY fnMYTABLEUDF(c.customer_no) as f
-
Same type, same nature and meaning values MUST BE STORED IN THE SAME COLUMN.
Follow the relational model rules and you'll avoid triubles.
_____________
Code for TallyGenerator -
Are you saying that you want to pass a CSV variable (ie. 'col1,col3,col5') to a stored procedure and have it return a result set with col1, col3, and col5 in it?
Or, are you saying that you want to pass a CSV variable (ie. '1,2,3,4,5') and create a table with a single column in it like...
- 1
- 2
- 3
- 4
- 5
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How the data currently lives:
Response MYId
First Response, Second, Third XYZ1234
Hello, How, Are, You UUU3333
The data was not placed in a one to many table from the beginning, but instead of normalizing the data, they would like this to be:
Response1 Response2 Response3 MyId
FirstResponse Second Third XYZ1234
Hello How Are UUU3333
I can then join the ID on another table to add this to another view
-
This is not how the data needs to be modeled... unless this is for some special report???
It should be like
XYZ1234, First
XYZ1234, Second
XYZ1234, Third
UUU3333, Hello
UUU3333, How
UUU3333, Are
UUU3333, You?
How are you going to manage the different number of words with your current method?
-
Yes, it is for a report.
-
With what application are you going to show the report?
-
They want it in a flat file.
-
I started something like this....but needs work
CREATE TABLE #ResponseList
(
OpportunityId varchar(15),
Reason1 varchar(150),
Reason2 varchar(150),
Reason3 varchar(150),
Reason4 varchar(150)
)
Declare cOpp CURSOR FOR
SELECT REASON, OpportunityId FROM OPPORTUNITY WHERE (NOT (REASON IS NULL))
Declare @OpportunityID varchar(12)
Declare @Reason varchar(250)
Declare @sql varchar(500)
Set @sql = 'SELECT '
DECLARE @Response varchar(150), @Pos int, @ResponseList varchar(250)
DECLARE @Response1 varchar(150), @Response2 varchar(150), @Response3 varchar(150), @Response4 varchar(150)
Open cOpp
FETCH NEXT FROM cOpp INTO @Reason, @OpportunityId
WHILE @@Fetch_status = 0
BEGIN
SET @ResponseList = @Reason
SET @ResponseList = LTRIM(RTRIM(@ResponseList))+ ','
SET @Pos = CHARINDEX(',', @ResponseList, 1)
IF REPLACE(@ResponseList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Response = LTRIM(RTRIM(LEFT(@ResponseList, @Pos - 1)))
IF @Response <> ''
BEGIN
IF (@Response1 IS NULL) BEGIN SET @Response1 = @Response END
ELSE IF (@Response2 IS NULL) BEGIN SET @Response2 = @Response END
ELSE IF (@Response3 IS NULL) BEGIN SET @Response3 = @Response END
ELSE IF (@Response4 IS NULL) BEGIN SET @Response4 = @Response END
INSERT INTO #ResponseList (OpportunityId,Reason1,Reason2,Reason3,Reason4) VALUES (@OpportunityId, @Response1, @Response2 , @Response3 , @Response4 )
END
SET @ResponseList = RIGHT(@ResponseList, LEN(@ResponseList) - @Pos)
SET @Pos = CHARINDEX(',', @ResponseList, 1)
END
END
FETCH NEXT From cOpp
End
Close cOpp
DEALLOCATE cOpp
-
Lucky,
In the example you gave (yeah, I know... just an example but this is really important), you dropped the 4th parameter (Are) from the 2nd line. How many CSV parameters are you expecting (required knowledge for the report) and how many do you want to show up on the report. Not busting your chops and I certainly don't envy the position you've been put in, but I need to know these little nuances to be able to give you what you need.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
, it can be done in SQL 2005 by using CROSS APPLY operator as follows: -Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply