SQL Multiple values on same row

fmhexpert
fmhexpert used Ask the Experts™
on
I have the following code:

select distinct
cd_value
from  custom_booking_data
where custom_data_urn = '******'
and field_name = 'spec_needs'

produces:
AAAAA
BBBBBB
CCCCC
ect.

I need it like this AAAAA,BBBBB,CCCCC,ect.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Which version of SQL Server, 2005 and above?

Author

Commented:
this is on SQL 2000
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
select stuff((select distinct ',' + cd_value
      from custom_booking_data
      where custom_data_urn = '******'
      and field_name = 'spec_needs'
      for xml path('')), 1,1, '') AS oneColumn
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
2000 did not like the xml code
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I jumped the gun, sorry for 2000:

declare @s varchar(8000)
select @s = coalesce(@s + ',', '') + cd_value
from
(
      select distinct cd_value
      from custom_booking_data
      where custom_data_urn = '******'
      and field_name = 'spec_needs'
) x
select @s as OneColumn

Author

Commented:
thank you

Commented:
DECLARE @dta VARCHAR(1024)
 
SELECT
    @dta = COALESCE(@dta + ',', '') + cd_value
FROM
    custom_booking_data
WHERE
      custom_data_urn = '******'
AND      field_name = 'spec_needs'    
 
 
SELECT myoutput = @dta
GO
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Did something funny happen with the closing process?

Author

Commented:
yes it did

Author

Commented:
I have another question

how do add in the custom_data_urn so it prints
ID              One_Column
XXXXX     AAAA,BBB,CCC
YYYYY     DDD,BBB,CCCC
Expert of the Quarter 2010
Expert of the Year 2010
Commented:

/* -- test table
create table custom_booking_data(
	cd_value varchar(10), custom_data_urn varchar(10), field_name varchar(10))
insert custom_booking_data
select 'aaaa', '******', 'spec_needs' union all
select 'bbb', '******', 'spec_needs' union all
select 'c', '******', 'spec_needs' union all
select 'dddd', '***aa*', 'spec_needs' union all
select 'dddd', '***aa*', 'spec_needs'
*/

-- create this function
create function distinct_custom_booking_data(
	@urn varchar(10), @fieldName varchar(10))
returns varchar(8000)
as
begin
declare @s varchar(8000)
select @s = coalesce(@s + ',', '') + cd_value
from
(
	select distinct cd_value
	from custom_booking_data
	where custom_data_urn = @urn
	and field_name = @fieldName
) X
return @s
end
GO

-- sample usage:

select custom_data_urn, dbo.distinct_custom_booking_data(custom_data_urn, field_name)
from custom_booking_data
group by custom_data_urn, field_name

Open in new window

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial