help in sql query version sql 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help in sql query version sql 2000

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2011-08-09 : 08:37:08

Dear All,

i have following table structure

create table test (
m_id int,
ref_id, int,
mcode varchar(10)
)

and following is the sample data

insert into test values (1,1,'East')
insert into test values (2,1,'East')
insert into test values (3,1,'East')
insert into test values (2,2,'west')
insert into test values (1,3,'north')
insert into test values (2,3,'north')
insert into test values (7,4,'south')

i want following out put

ref_id main_ids mcode
1 2,3 east
2 2 west
3 1,2 north
7 4 south

Farid

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-09 : 09:18:52

If it is always only two values for mainid:

--need to do a CASE to remove comma for singles
select refid, convert(varchar(250),min(mainid)) + ','+ convert(varchar(250),max (mainid)) ,mcode from test

gfaryd
Starting Member

27 Posts

Posted - 2011-08-09 : 09:44:12

no values are not static they can vary from 1 to 30

Farid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:21:26

you need to create a udf for that and use it like


SELECT DISTINCT ref_id,dbo.CSVList(ref_id),mcode 
FROM Table


and CSVlist will be like

CREATE FUNCTION dbo.CSVList
(
@Ref_id int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetStr Varchar(8000)

SELECT @RetStr=COALESCE(@RetStr,'') +CAST(m_id as varchar(10))
FROM Table
WHERE ref_id=@Ref_id

RETURN @RetStr
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources