CODEWITHSUNDEEP
c#sql-serverstored-proceduresreturn-value
lxmkv
lxmkv

Reputation: 81

Return one column values as list from stored procedure

Is it possible to return data structure like this directly from MSSQL ?

public class MyClass
{
       public int Id {get; set;}
       public int List<int> AnotherIds {get; set;}
}

I need this to retrieve data to list if Id is a duplicate For example: Select * FROM MyTable

--------------------
|   Id    | AnthId |
|   1     |   1    |
|   1     |   2    |
|   1     |   3    |
|   2     |   1    |
|   2     |   2    |
|   2     |   3    |
|   2     |   4    |
--------------------

Result will be List of 2 entities: MyClass[0]{1, [1,2,3]} MyClass[1]{2, [1,2,3,4]}

Upvotes: 1

Views: 1977

Answers (1)

Yuri
Yuri

Reputation: 2900

Yes, it is possible. I'm including an example you can copy/paste into your query window and using this example build your SQL to return desired data:

declare @tbl table(ID int, AnotherID int)
declare @aa varchar (200)
declare @result table(ID int, AnotherIDs varchar(200))

set @aa = ''

insert into @tbl (ID, AnotherID) Values(1,1)
insert into @tbl (ID, AnotherID) Values(1,2)
insert into @tbl (ID, AnotherID)Values(1,3)
insert into @tbl (ID, AnotherID) Values(1,4)

insert into @tbl (ID, AnotherID) Values(2,1)
insert into @tbl (ID, AnotherID) Values(2,2)
insert into @tbl (ID, AnotherID) Values(2,3)
insert into @tbl (ID, AnotherID) Values(2,4)

--select * from @tbl


declare @i int
select @i = min(ID) from @tbl
declare @max int
select @max = max(ID) from @tbl

while @i <= @max begin

select   @aa = 
        coalesce (case when @aa = ''
                       then CAST(AnotherID as varchar)
                       else @aa + ',' + CAST(AnotherID as varchar)
                   end
                  ,'')
      from @tbl where ID=@i

insert into @result(ID, AnotherIDs)
values(@i, @aa)

        set @aa=''

set @i = @i + 1
end

select * from @result

Result looks like this:
ID AnotherIDs
1 1,2,3,4
2 1,2,3,4

Upvotes: 1

Related Questions