SQL Server - Query Problem

Hi,

Please refer http://blog.sqlauthority.com/2007/08/09/sql-server-2005-list-all-the-column-with-specific-data-types/ which has the store procedures for displaying the all the column with specific data types. You can take the values from column temp_id into one array say temp[] and correponding temp_name into array name[]. Afetr that you can sort both the arrays and display the result.

Regards,

Paresh

You have to loop through records using a Cursor.

Inside your stored procedure -perform the steps:

1. create a sql server TEMPORARY table with temp_id and temp_name columns(give sufficient size for temp_name)

2. loop through the records in temp table for each temp_id  using a Cursor

3.Concatenate list of templ_names for the specific temp_id into a variable

4. Insert concatenated list of templ_names  and specific temp_id into TEMPORARY table

5. continue with looping

6. Finally select records from TEMPORARY table and return resultset

You can add a check whether a record exist in TEMPORARY table for the specific temp_id before concatenating.

Hi,

try the below Stored Procedure

create procedure GetValues
as

create table tempDetials(temp1_id int, temp1_name varchar(1000))

declare @Id int
declare @IDNew int
declare @OldVal varchar
declare @NEwVal varchar
declare @details varchar(4000)

DECLARE cur_Det CURSOR FOR
SELECT temp_id, temp_name from [temp]

OPEN cur_Det
FETCH NEXT FROM cur_Det into @IDNew, @NEwVal
set @Id = @IDNew
set @OldVal = @NEwVal

while @@FETCH_STATUS = 0
BEGIN
   if(@Id == @IdNew)
   begin
     set @details = @details + "," +  @NEwVal
   end   
   else
   begin
        insert into tempDetials select @Id, @details
        set @Id = @IDNew
 set @OldVal = @NEwVal
 set @details = ''
   end
   FETCH NEXT FROM cur_Det into @IDNew, @NEwVal
END

CLOSE cur_Det
DEALLOCATE cur_Det

select * from tempDetials
drop table tempDetials

Create Table Table1

id int,

name varchar(50)

declare abc cursor for

select id from temp1 Group By id

OPEN abc

Declare @Id int

FETCH NEXT FROM abc

INTO @Id

WHILE @@FETCH_STATUS = 0

BEGIN

declare @nameConcat varchar(50)

declare xyz cursor for

select name from temp1 where id=@id

Open xyz

Declare @name varchar(50)

set @nameConcat=''

FETCH NEXT FROM xyz

INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

if @nameConcat=''

Begin

set @nameConcat=@name

End

else

Begin

set @nameConcat=@nameConcat + ', ' + @name

End

FETCH NEXT FROM xyz

INTO @name

END

DEALLOCATE xyz

insert into Table1 values(@Id,@nameConcat)

FETCH NEXT FROM abc

INTO @Id

END

DEALLOCATE abc

select * From Table1

Drop Table Table1

Hello VasanthaKumar,

I just tried this Procedure with the values as given by Shekhar.... but i m not able to get the result he wanted......

also I wanted to know that with this Cursor you r checking just the adjacent two values ......

So, what if the values are like .......

id name

1  abc

2  xyz

1  def

Will this Procedure work for that also.........

I wanted to know coz to accomplish this result I m using two cursors which slows the Execution Speed when the Data will increase....

Hi,

You can use the same command and replaced the required values while creating the cursor as following

CREATE CURSOR alias_name 
...[CODEPAGE=nCodePage]
   (fname1 cFieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] 
   [CHECK lExpression [ERROR cMessageText]] 
   [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]]
   [DEFAULT eExpression] [UNIQUE [COLLATE cCollateSequence]]
   [NOCPTRANS] [, fname2 ...]) 
   | FROM ARRAY ArrayName

Please refer http://msdn.microsoft.com/en-us/library/scxa5w3s(VS.80).aspx for more details.

Regards,

PAresh

Hi,

here is the udpate procedure....

alter procedure GetValues
as

create table tempDetials(temp1_id varchar(100), temp1_name varchar(1000))

declare @Id varchar
declare @IDNew varchar
declare @OldVal float
declare @NEwVal float
declare @details varchar(4000)
declare @RowCo int
declare @TotlaRow int

set @Rowco = 0
set @TotlaRow = 0

DECLARE cur_Det CURSOR FOR
SELECT Momth, amo from det

select @TotlaRow = count(*) from det

OPEN cur_Det
FETCH NEXT FROM cur_Det into @IDNew, @NEwVal
set @Id = @IDNew
set @OldVal = @NEwVal

while @@FETCH_STATUS = 0
BEGIN
   set @rowCo = @rowCo + 1
   if(@Id = @IdNew)
   begin
     set @details = @details + ',' +  cast(@NEwVal as varchar)
     print 'Detail - ' + @details
     print cast(@rowCo as varchar) + ' - ' +  cast(@@CURSOR_ROWS as varchar)
     if @rowCo = @TotlaRow
     insert into tempDetials select @Id, @details

   end   
   else
   begin
        insert into tempDetials select @Id, @details
        set @Id = @IDNew
 set @OldVal = @NEwVal
 set @details = @NEwVal
        if @rowCo = @TotlaRow
      insert into tempDetials select @Id, @details
   end
   FETCH NEXT FROM cur_Det into @IDNew, @NEwVal
END

CLOSE cur_Det
DEALLOCATE cur_Det

select * from tempDetials
drop table tempDetials

Hi,

its simple only and no need for two cursor...

1. Create the cursor with details from table

2. assign the first record Id to variable

3. in loop check the new Id and old Id, if both are same, concatenate the values

4. Otherwise insert the details to temp table and asssign the next record values to old and new Id variable

thats all....

this will work for the above type of records too.....