SQL Server - Query Problem - Asked By shekhar kumar on 10-Sep-08 05:25 AM

I have a table temp like this

    temp_id             temp_name
      1                     shekhar
      1                     ranjan
      1                     ram
      2                     manoj
      ............
      ....... ...
Now I want to create a store procedure which return values like this

       temp1_id              temp1_name
          1                  shekhar, ranjan, ram
          2                  manoj,.....
         .............

please help me
Thanks in advance

SHEKHAR

Get values into arrays - Perry replied to shekhar kumar on 10-Sep-08 05:42 AM

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

RE : Query Problem - chandra kumar replied to shekhar kumar on 10-Sep-08 05:46 AM

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.


Re : Query Problem - Vasanthakumar D replied to shekhar kumar on 10-Sep-08 05:50 AM

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

reply - Binny ch replied to shekhar kumar on 10-Sep-08 06:06 AM
create procedure procName @varName int as select temp_id, temp_name from tableName where temp_id = @varName
Try this............. - Varsha Ramnani replied to shekhar kumar on 10-Sep-08 06:07 AM

Hi, Try this........... U can also make one Table valued Function which will Return a Table

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

I want to ask something about this Procedure - Varsha Ramnani replied to Vasanthakumar D on 10-Sep-08 06:15 AM

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

Thanks - shekhar kumar replied to Vasanthakumar D on 10-Sep-08 06:39 AM
Thanks a lot for so quick response. Can it be done without using cursor.
Thanks - shekhar kumar replied to Varsha Ramnani on 10-Sep-08 06:42 AM
I have tried it and it is working fine. But I think it can be done without using cursor. Using rowid. I am not sure but I have seen a post on other site for looping in table row. Thanks
using cursor - Perry replied to shekhar kumar on 10-Sep-08 06:43 AM

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

here is the Update one... - Vasanthakumar D replied to shekhar kumar on 10-Sep-08 07:04 AM

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

Here is the Logic... - Vasanthakumar D replied to Varsha Ramnani on 10-Sep-08 07:14 AM

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