SQL Query Formatting - SQL Server Forums

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

SQL Query Formatting

Author

Topic

abuhassan

105 Posts

Posted - 2006-05-24 : 05:46:01

Hi

Im new to sql server what i wanted to do was as follows:

i got a table with application_name and app_version

it looks like as follows

Application_name App_ver
-----------------------------
Oracle 8.1.0
Oracle 8.5
Oracle 9.1
Oracle 10.1
Sql Server 6.5
Sql Server 7
Sql Server 2000
Sql Server 2005

i want to get query the table and produce an out put as follows:

Application_name App_vers
-------------------------------------------
Oracle 8.1.0, 8.5, 9.1, 10.1
Sql Server 6.5, 7, 2000, 2005

please note i need the commas in place
i have written several queries to try and do it but no luck

im using sql server 2000

i will be greatful if any one can help...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 05:55:26

This is a commonly asked question, and this link should help you...

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

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

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

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-24 : 05:57:36

see
http://www.nigelrivett.net/SQLTsql/CSVStringFromTableEntries.html

Create the function
create function csvtbl
(@id varchar(100))
returns varchar(1000)
AS
begin
declare @csv varchar(1000)
select @csv = coalesce(@csv+',','') + App_ver
from mytbl
where Application_name = @id
return @csv
end
go

then
select Application_name, App_vers = dbo.csvtbl(Application_name)
from
(select distinct Application_name from mytbl) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-05-24 : 07:25:59

hi nr

i tried the code using the function vbs

Oracle Data Provider for .NET Help 10.1.010
Oracle JInitiator 1.1.8.14
Oracle8i Client 8.1.7.0,8.1.7.0,8.1.7.0,8.1.7.0,8.1.7.0
OracleClient9.2 1.0.0,1.0.0,1.0.0,1.0.0

it seemed to have duplicated the values?
is it possible to put ascii char(13) to delminate them.... also does sql server have an export as csv option?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-24 : 07:43:18

Don't see how thgat could happen. Do you have duplicate values in your table?

try select App_ver from mytbl where Application_name = 'Oracle8i Client'
if so use a derived table to get distinct values in the function.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-05-24 : 08:43:52

Thanks yes there were duplicates i have done distinct and its fine thank really appreciate the the help

one quick question instead of deliminating the with a comma can i deliminate them using carriage return ascii char(13) i tried it by replacing the comma it didnt work? any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-24 : 09:00:26

Create the function
create function csvtbl
(@id varchar(100))
returns varchar(1000)
AS
begin
declare @csv varchar(1000)
select @csv = coalesce(@csv+char(13),'') + App_ver
from mytbl
where Application_name = @id
return @csv
end
go

you might want char(13) + char(10).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-05-24 : 09:05:33

Thanks

first time im using sql team impressed with the response.....

keep the good work up....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources