asked on

values from all rows in a string

Hello,

I would like to get all values from the column in a string.
In this example I would like to get '2+7' How is best to do that?

Here below I try to use cursor to add to @fString. But I get NULL when I select it.

DECLARE @fString varchar(100);

DECLARE @MyTableK table(Unit varchar(100))
INSERT INTO @MyTableK(Unit)values('2')
INSERT INTO @MyTableK(Unit)values('+')
INSERT INTO @MyTableK(Unit)values('7')
		
DECLARE @fid varchar(100)
DECLARE f_Cursor CURSOR FOR
SELECT Unit FROM @MyTableK
OPEN f_Cursor;
	FETCH NEXT FROM f_Cursor
	INTO @fid;
	WHILE @@FETCH_STATUS = 0
	   BEGIN
			SET @fString=@fString+@fid
	   FETCH NEXT FROM f_Cursor
			INTO @fId;
	   END;
	   CLOSE f_Cursor;
DEALLOCATE f_Cursor;

SELECT @fString

Open in new window

Kent Dyer

With this example, you are performing three rows of inserts: 2, +, 7...
INSERT INTO @MyTableK(Unit)values('2')
INSERT INTO @MyTableK(Unit)values('+')
INSERT INTO @MyTableK(Unit)values('7')

I think you what you want do is:

INSERT INTO @MyTableK(Unit)values(STR('2 + 7'))

If you want to do it exactly as you showed '2 + 7'..

INSERT INTO @MyTableK(Unit)values('''2 + 7''')

You may even have to do something like:

INSERT INTO @MyTableK(Unit)values(CHAR(39)+'2'+CHAR(43)+'7'+CHAR(39))

HTH,

Kent

johnson1

ASKER

Hi,
No I have to get it from the table. This was just a simple example.

johnson1

ASKER

Hi,
Does someone know why this does not work?

Anthony Perkins

Does someone know why this does not work?
To answer your specific question: Concatenate a NULL and you get a NULL so either initialize
@fString to and empty string ('') or use ISNULL.

Having said that, resort to using a CURSOR to do such a simple task is way over-kill and not efficient at all and performance mayl be abysmal. There are far better ways of doing that. Let me know if you are interested.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Anthony Perkins

if you want to persist with using a CURSOR then either do it like this:

DECLARE @fString varchar(100) = '';

DECLARE @MyTableK table(Unit varchar(100))
INSERT INTO @MyTableK(Unit)values('2')
INSERT INTO @MyTableK(Unit)values('+')
INSERT INTO @MyTableK(Unit)values('7')
		
DECLARE @fid varchar(100)
DECLARE f_Cursor CURSOR FOR
SELECT Unit FROM @MyTableK
OPEN f_Cursor;
	FETCH NEXT FROM f_Cursor
	INTO @fid;
	WHILE @@FETCH_STATUS = 0
	   BEGIN
		SET @fString=@fString+@fid
	   	FETCH NEXT FROM f_Cursor INTO @fId;
	   END;
	   CLOSE f_Cursor;
DEALLOCATE f_Cursor;

SELECT @fString

Open in new window

Or like this:

DECLARE @fString varchar(100);

DECLARE @MyTableK table(Unit varchar(100))
INSERT INTO @MyTableK(Unit)values('2')
INSERT INTO @MyTableK(Unit)values('+')
INSERT INTO @MyTableK(Unit)values('7')
		
DECLARE @fid varchar(100)
DECLARE f_Cursor CURSOR FOR
SELECT Unit FROM @MyTableK
OPEN f_Cursor;
	FETCH NEXT FROM f_Cursor
	INTO @fid;
	WHILE @@FETCH_STATUS = 0
	   BEGIN
		SET @fString = ISNULL(@fString, '') + @fid
	   	FETCH NEXT FROM f_Cursor INTO @fId;
	   END;
	   CLOSE f_Cursor;
DEALLOCATE f_Cursor;

SELECT @fString

Open in new window

Pick your poison.

Surendra Nath

you can use the below method, for getting the result, if a plus sign is already present in your table, but if there is a chance that you want to add plus sign in between every row before converting that into a column then use the method 2

method 1

DECLARE @MyTableK TABLE (Unit varchar(100))
INSERT  @MyTableK (Unit)
VALUES  ('2'), ('+'), ('7')

select ''+ unit from @MyTableK for xml path('')

Open in new window

method 2

DECLARE @MyTableK TABLE (Unit varchar(100))
INSERT  @MyTableK (Unit)
VALUES  ('2'),  ('7')

select stuff((select '+'+ unit from @MyTableK for xml path('')),1,1,'')

Open in new window

more about the above technique can be found in my blog here http://everysolution.wordpress.com/2011/08/12/concatenate-rows-into-a-column-separated-by-a-delimiter/

johnson1

ASKER

Thank you.