Multiple Resultset in a single resultset (UNION)

Question

Post reply

Multiple Resultset in a single resultset (UNION)

Girish Sharm

Valued Member

Points: 62
More actions
January 29, 2009 at 11:33 pm

#126002

I am using :
Microsoft SQL Server Management Studio 9.00.1399.00 on windows XP SP-3.
In the query analyzer i am giving the following code:
DECLARE @position int, @nstring nchar(50)
SET @position = 1
SET @nstring = N'MyUnicodeString'
WHILE @position <= DATALENGTH(@nstring)
BEGIN
SELECT @position,
CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),
UNICODE(SUBSTRING(@nstring, @position, 1))
SELECT @position = @position + 1
END
It is returning 100 result sets. How do i get the output of the above code in a single string with "," seperated and update the all columns of my table.
Thanks & Regards
Girish Sharma

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

See the following...

http://www.sqlservercentral.com/articles/Test+Data/61572/

Better question would be, what are you really concatenating and why?

--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)

Girish Sharm Valued Member Points: 62 More actions · Answer 2

Thanks for your reply and provided link.

Actually i am having a table of 3 columns which is containing around 48000 rows having Unicode data. With the above code i am able to get decimal value of every character of the Unicode string. I wish that all the decimal value should be Concatenated and replaced another column of the same table.

Please guide me what should i code additionally to get update the column with the values returned by above code in a single string.

Thanks & Regards

Girish Sharma

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 3

Girish,

I believe that you can go for COALESCE function.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Girish Sharm Valued Member Points: 62 More actions · Answer 4

I am using following code to update the "the" column of villages table. It is working but not updating the rows when i check the table. In the table "tehsil" column contains Unicode Data.

DECLARE @position int, @nstring nchar(50),@mystring nvarchar(100),@currow float

SET @position = 1

SET @currow = 0

table_loop:

while @currow <=2

BEGIN

set @currow=@currow+1

SET @mystring = ''

select @nstring=tehsil from villages where id=@currow

if @nstring is nullgoto table_loop

WHILE @position <= DATALENGTH(@nstring)

BEGIN

set @mystring = @mystring+rtrim(ltrim(STR(UNICODE(SUBSTRING(@nstring, @position, 1)))))+'/'

SELECT @position = @position + 1

if UNICODE(SUBSTRING(@nstring, @position, 1)) is null

BEGIN

PRINT 'UPDATING ROW:'+STR(@CURROW)

UPDATE VILLAGES SET the=@mystring where id=@currow and the is null

BREAK

END

GO

I am sure i am doing little mistake; but not able to find it. Please help me.

Thanks & Regards

Girish Sharma

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 5

I believe you can do it very easily without two loops.

Just give some sample data along with O/P required.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Girish Sharm Valued Member Points: 62 More actions · Answer 6

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Villages](

[ID] [float] NOT NULL,

[TEHSIL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CIRCLE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[VILLAGE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[THE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CIR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[VIL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_Villages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO DBO.VILL VALUES(1,'?????','?????','??????????',NULL,NULL,NULL)

INSERT INTO DBO.VILL VALUES(2,NULL,NULL,'????????',NULL,NULL,NULL)

Now, i have to replace THE,CIR,VIL columns with something like this:

2309/2332/2350/2375/2352/. Here 2309,2332,2350,2375,2352 is the decimal values of Unicode '?????' string.

Thanks & Regards

Girish Sharma

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

I still would like to know the business reason behind this...

--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)

Girish Sharm Valued Member Points: 62 More actions · Answer 8

The data is in Unicode Character and our user will use this in VB.NET application. He is going to search a particular village, he wish to filter the data on a particular circle, he wish to apply some fonts on the report output; how he will do that. If there is column in the table having values like 2309/2332/2350 as a string something like this; then i will add new columns and will then be replaced by Hindi (Indian Language) character values depending upon 2309 means '?' 2332 means '?' for example etc.

Regards

Girish Sharma

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

I think that's begging for trouble, but thank you for the explanation.

--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)

bvaljalo-1000038 Right there with Babe Points: 760 More actions · Answer 10

Shouldn't you finish building the entire 'new' unicode string before you fire the update statement? The way it looks to me, you're trying to update the field [the] over and over again, once for each letter in the original field. However, that update won't happen after the very first letter has been written, due to the 'where the is not null'.

I think you just need to move the update statement out of the innermost loop ...