Multiple Database Owners

Hi Forum,

Can a DataBase havemore than one DBOwner?

If so, is there a query to return the list ofDBOwners(dbo)?

Regards,

Naveen

naveenj:

Can a DataBase havemore than one DBOwner?

http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2003-06/0063.html

naveenj:

If so, is there a query to return the list ofDBOwners(dbo)?

http://www.msdner.com/forum/thread142280.html

Please let me know if you need more help.

Good luck.

MULTIPLE COLUMNS using and GROUP BY

I hope there's someone that is able to help me. I'm new to SQL Server 2005 and have come across a pretty tough problem.

I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though.

Here's the SQL statement:

"SELECT City_Name, Province_ID, City_PostCode
FROM Cities
WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode
AND City_Name != '" + exclude
ORDER BY Province_ID ASC, City_PostCode ASC ";

I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem.

Can anyone help me?

hi,

Well, if you have different zip codes to the same City, you should have duplications and no matter what you use; distinct or group by.

eg:

City_name Province_ID City_PostCode

Los Angeles CA 90150

Los Angeles CA 90151

In this case, you should not have one record, based on your select statement. Maybe you should appear a zip code range, instead of individual zips, then you should have distinct result set.

Also, if just change the second zip to the first one, and using distinct you will have one record. you do not need to specify column level the distinct, it applied on all enumerated column in the select statement.

I hope it helps.

Kind Regards,

Janos

|||

If you want to show the multiple postal codes as single row then you can use the following query.. One row per city with out data loose..

Code Snippet

Create Table #cities (

[City_name] Varchar(100) ,

[Province_ID] Varchar(100) ,

[City_PostCode] Varchar(100)

);

Insert Into #cities Values('Los Angeles','CA','90150');

Insert Into #cities Values('Los Angeles','CA','90151');

Insert Into #cities Values('Austin','TX','73301');

Select

Distinct

Main.City_name

, Main.Province_ID

, Substring((Select ',' + City_PostCode as [text()]

From #cities Sub

Where Sub.City_name = Main.City_name

And Sub.Province_ID = Main.Province_ID For XML Path('')),2,8000) as [City_PostCodes]

From

#cities as Main

|||Thanks Jano, I updated the statement to reflect the following:

"SELECT DISTINCT City_Name, Province_ID FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' )
ORDER BY Province_ID ASC ";

The only thing is I can't sort the selections according to there postal codes. however the intial problem of the distinct fields is resolved.

Thanks again.

Regards,
Simon
|||

You can sort by postal_code (using OVER clause)..

"SELECT DISTINCT City_Name, Province_ID,Max([City_PostCode]) Over (Partition By City_name,Province_ID) as [City_PostCode] FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' ) ORDER BY [City_PostCode] ASC ";

Multiple Columns into Single Row -- Very urgent

Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like this

The query looks like this
Select ID, TYPE, VALUE From myTable Where filtercondition = 1

ID TYPE VALUE
1 type1 12
1 type2 15
2 type1 16
2 type2 19

Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returns

ID TYPE1 TYPE2 VALUE1 VALUE2
1 type1 type2 12 15
2 type1 type2 16 19

Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.

I've done something like this, but not in SQL. What I do is build a datatable from my select results, populate it, and then return that to my caller. It works something like;

Get Results

Loope through results to get my columns (In my case there may not be a value for every type.)

Build datatable

Loop through results again, populating datatable.

Return datatable