String values in field across into varchar column

[rlevasseur]

I'm not sure if anyone can help me on this, and have not been able to find an example anywhere. I will give an oversimplified example here

TestTbl
Region State
---- ----
NEA CT Select Region, State
NEA MA From TestTbl
NEA ME Where Region = 'NEA'
NEA NH
NEA RI
NEA VT

What I would like the output to look like would be 1 record per region, with state values strung into a varachr column

Region States
NEA 'CT MA ME NH RI VT'

Some users have asked for a small enhancement to a report I have developed. WHat they woul like is a fotter line with children rolling up to level for report they selected.

The stored proc I wrote is dynamic and allows then to summarize data at any level. I want to tack on a column
containing the lower level child info.

Any help much appreciated

Tanks, Ray

 

[mwolf00]

Hope this helps:

create proc stringEm
AS
Set nocount on

create table testtbl(
stateID INT IDENTITY,
region char(3),
state char(2)
)

insert into testtbl VALUES ('NEA','CT')
insert into testtbl VALUES ('NEA','MA')
insert into testtbl VALUES ('NEA','ME')

DECLARE @thisState INT
DECLARE @maxState INT

SELECT @thisState = MIN(stateID) FROM testTbl
SELECT @maxState = MAX(stateID) FRoM testTbl


create table #output(
	region char(3),
	states varchar(200)
)
insert into #output (region, states) SELECT distinct region,'' from testtbl

WHILE @thisState <= @maxState
BEGIN
	UPDATE #output SET states = states + state + ' ' FROM testtbl where stateID = @thisState
	select @thisState = @thisSTate + 1
END

select * from #output
go

 

[rlevasseur]

Thanks for your help. I have begun adding a variation of your code example to my stored procedure. True to form, the user has said, "yeabut", and would like the child members of multiple rollup structures available, 1 record per rollup.

The way I created the web report allows users to make multiple selections form a select list, then passes values to sql as an in () list. The current code works fine of they select a single value. I might be able to do something with a cursor.

so now they want

NEA 'MA ME NH CT VT RI'
SWA 'CA AZ NV ......'
SEA 'FL AL LA GA....'

But thanks again for a solution I would probably have not thought of.

 

[tlbroadbent]

See faq183-2146. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.