Urgent help require for Following Query

Question

Post reply

Urgent help require for Following Query

Neel-260723

SSC Journeyman

Points: 80
More actions
May 4, 2006 at 11:59 pm

#168126

Hi,
I have table like this.
ProjectName     Sector
A                         M
A                         N
A                        O
B                       L
C                        P
C                        Q
D                         R
E                        S
E                         T
I need query that returns like

ProjectName     Sector
A                         M,N,O
B                        L
C                        P,Q
D                        R
E                         S,T
Please mail me on neel@eqqumail.com
thanks and regards
Neel

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

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

[KH] SSCommitted Points: 1908 More actions · Answer 1

see here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

sherin SSC Journeyman Points: 77 More actions · Answer 2

Please try this stored procedure.You can use your table instead of @sourceTbl used in this sample

create procedure sptest as

begin

DECLARE @sourceTbl TABLE (proj varchar(50),Sector varchar(50))

DECLARE @ResultTbl TABLE (proj varchar(50),Sector varchar(200))

insert into @sourceTbl values('a','Sector1')

insert into @sourceTbl values('a','Sector2')

insert into @sourceTbl values('a','Sector3')

insert into @sourceTbl values('b','Sector4')

insert into @sourceTbl values('b','Sector5')

insert into @sourceTbl values('b','Sector6')

insert into @sourceTbl values('c','Sector7')

insert into @sourceTbl values('c','Sector8')

insert into @sourceTbl values('c','Sector9')

insert into @sourceTbl values('d','Sector10')

DECLARE @proj varchar(50)

DECLARE @i int

DECLARE @tempSectors VARCHAR(200)

declare tmpcurs cursor for select distinct proj from @sourceTbl

open tmpcurs

fetch next from tmpcurs into @proj

WHILE @@FETCH_STATUS = 0

BEGIN

SET @tempSectors = ''

SELECT @tempSectors =

CASE @tempSectors

WHEN '' THEN Sector

ELSE @tempSectors + ', ' + Sector

END

FROM @sourceTbl

WHERE proj=@proj

INSERT INTO @ResultTbl (proj, Sector) VALUES (@proj, @tempSectors)

fetch next from tmpcurs into @proj

END

close tmpcurs

deallocate tmpcurs

SELECT * FROM @ResultTbl

end

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Hi Neel,

The function method given in the link provided by zh is usually considered best for this type of problem. Here's that method applied to the example given...

--data

if object_id('zMyTest') is not null drop table zMyTest

create table zMyTest (ProjectName varchar(50), Sector varchar(50))

create unique clustered index Ix_zMyTest on zMyTest (ProjectName, Sector)

insert zMyTest

select 'A', 'M'

union all select 'A', 'N'

union all select 'A', 'O'

union all select 'B', 'L'

union all select 'C', 'P'

union all select 'C', 'Q'

union all select 'D', 'R'

union all select 'E', 'S'

union all select 'E', 'T'

--function

if object_id('dbo.zFnConcatenateSectors') is not null drop function dbo.zFnConcatenateSectors

go

create function dbo.zFnConcatenateSectors (@ProjectName varchar(50))

returns varchar(8000)

begin

declare @s-2 varchar(8000)

set @s-2 = ''

select @s-2 = @s-2 + Sector + ',' from zMyTest where ProjectName = @ProjectName order by Sector

set @s-2 = left(@s, len(@s)-1)

return @s-2

end

go

--calculation

select ProjectName, dbo.zFnConcatenateSectors(ProjectName) as Sector

from (select distinct ProjectName from zMyTest) a

--tidy up

if object_id('zMyTest') is not null drop table zMyTest

if object_id('dbo.zFnConcatenateSectors') is not null drop function dbo.zFnConcatenateSectors

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Neel-260723 SSC Journeyman Points: 80 More actions · Answer 4

thanks a lot friends,

i got solution for this hints.

regards

Neel