|
Hi,
I need to have a column value from multiple rows as one value
e.g. select name from employee
returns> name
-----
david
malcom
rave
I need to have this value as
name
----
david, malcom,rave (in one row)
Please note that I am on sql server 2000
Can anyone help in generating such sql script.
regards
Vijay
|
|
|
|
|
Sounds like another homework question, but here you are:
DECLARE @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + s.Name
FROM MyTable s
SELECT @List
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob,
My query is in my asp.net code is as (which is not a stored procedure i am implementing it in my vb code.
select assets.asset_pk as UNIQUEID,
assets.asset_ID ,
assets.[name] collate database_default ,
asset_types.ASSET_TYPE_NAME ,
(CASE when IsNull(townland.townland, '') = '' THEN '' eLSE townland.townland END) ,
(CASE when IsNull(thoroughfares_fme1.thorfare_name, '') = '' THEN '' eLSE thoroughfares_fme1.thorfare_name END) ,
(CASE assets.LEASED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.DISPOSED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.ASSET_REGISTERED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
assets.ASSET_FOLIO_NUMBER ,
assets.FLAGGED_NOTE collate database_default +'<br>' AS DETAILS
from assets
INNER JOIN asset_types on assets.type_id = asset_types.asset_type_id
left join asset_streets on assets.asset_pk =asset_streets.asset_ref_key
left join thoroughfares_fme1 on asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex
left join asset_townlands on assets.asset_pk=asset_townlands.asset_ref_key
left join TOWNLAND on asset_townlands.TOWNLAND_KEY = TOWNLAND.primaryindex
where
assets.asset_ID like '%L%' or
assets.[name] collate database_default like '%L%' or
asset_types.ASSET_TYPE_NAME like '%L%' or
townland.townland like '%L%' or
thoroughfares_fme1.thorfare_name like '%L%' or
assets.leased_to like '%L%' or
assets.LEASED_CUSTOMER_ID like '%L%' or
assets.LEASED_OCCUPIER like '%L%' or
assets.DISPOSED_TO_NAME like '%L%' or
assets.ASSET_FOLIO_NUMBER like '%L%' or
assets.FLAGGED_NOTE collate database_default like '%L%'
order by assets.asset_ID
here because of thoroughfares_fme1.thorfare_name field I am getting rows for the asset_id
e.g
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER CONNAUGHTON ROAD
I want this records as one record
e.g.
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET, CONNAUGHTON ROAD
Now my datagrid is binded to thi record set.
When I tried to have your list concept in my code I get the error as it can be implemented in other data retrieval fields.
Now please tell me how I can proceed from here?
regards
|
|
|
|
|
Well, for a start this is a totally different question to your original one. My reply would work for what you asked in the first place, but this is a different problem.
I can only assume your join for asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex returns multiple rows, which kind of goes against the name of primaryindex.
Without reviewing your data (and no, I am not interested in seeing it) I can only suggest you look at introducing additional outer joins.
hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes there are mulltiple thorfare entries for the asset
so it produces my record set as
e.g.
1 abc thorfare1
1 abc thorfare2
where as I just want it as
1 abc thorfare1,thorfare2
regards
Vijay
|
|
|
|
|
Try creating a function to string all the thorfare entries together using the same technique as I showed in my original reply, then your select bceomes something like
select asset_name, dbo.fn_thorfar(assetid), ....
If you are not sure about functions try BOL, there are some good examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm not familar with SQL, I get around with the basics which usually works fine for me.
However, I've come up with a situation where the code will no not run from a SQLDatasource object in VS.
I have, however gotten it to run properly from SSMSE so I want to write it as a stored procedure. But I really have no idea how to even begin. I'm sure easy to do, I just have never done it before.
Here is the SQL that I got to run properly.
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,
dbo.Listings.TimesViewed
FROM dbo.Listings INNER JOIN
dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID INNER JOIN
Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID
WHERE (dbo.Listings.MemberID = @MemberID)
Any help with this would be greatly appreciated.
modified on Tuesday, August 26, 2008 8:19 PM
|
|
|
|
|
you are right, it is very simple. Read BOL.
CREATE PROC MyProc<br />
--Declare <br />
@MemberID INT<br />
<br />
As<br />
<br />
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,<br />
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,<br />
dbo.Listings.TimesViewed<br />
FROM dbo.Listings <br />
INNER JOIN dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID <br />
INNER JOIN Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID <br />
INNER JOIN Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID<br />
WHERE (dbo.Listings.MemberID = @MemberID)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The template provided in SSMSE was so very NOT helpful. All that extra stuff they put into the template just made it confusing.
Thanks in the future at least I will understand what I'm looking at LOL.
|
|
|
|
|
It can be usefull looking at someone elses procs (minus the BOL overkill), some of the MS sample databases are a good start (have not looked at one in years).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i tried to install SQL Server 2005 in my laptop with Vista home version, i also download SP2 package to update it. but still have problem,can anyone tell me if I need to install other package? thanks
|
|
|
|
|
What is the actual problem?
|
|
|
|
|
after I update sqlserver2005SP2, it said cannnot coonect to server...and later I cannot find sql serevr management studio icon and others ( previsouly I could see that when I finished installation)
|
|
|
|
|
|
i guess the problem is with IIS, i tried to enable that in control panel, but when I start to install, still one warning telling me IIS feature not found.
should i download a IIS 7 package and install it? if yes, where I can find that to download? thanks
|
|
|
|
|
This is just a warning. If you don't have IIS, you won't be able to use SQL Server's native XML Web Services. This doesn't affect Management Studio, which can be used without IIS.
|
|
|
|
|
Hi, i understand the concept behind the physical crystal report, i struggle with the vb code behind that.
For example...I have a table with customer details...i want to have a textbox on my aspx page where you can input a certain surname or a range...like between A and D...to display on the report
I struggle with the code...can you please help me!!! I have looked at quite a few tutorials but every tutorial does something else....please help!
If you can maybe give me a step by step explanaition on what everything must happen in the code....or give me a sample application that i can study?
|
|
|
|
|
Hi,
not sure if I understood your question correctly, but if you want to filter records by a user given value then:
- add a parameter field into the report (using field explorer)
- add a criteria to the report based on parameter field by using Record Selection Formula Editor
|
|
|
|
|
I had install ms sql server 2008 over a m/c which has a network connection too. but I'm unable to access this from remotly. in 2005 we just provide the remote connections allow (in connection property). But how to allow remote access in sql server 2008 ?
please help on this.
Thanks
rajram
raj
|
|
|
|
|
Using Sql Server Configuration Manager, check that you have appropriate protocols enabeld and correctly configured.
Mika
|
|
|
|
|
Hello All;
I have a SQL db that collects environmental data. As it gets larger the performance of the server suffers. So I think that I would like to copy the "working" db to a new db then purge the working db. The be able to run querrys that span the databases.
I don't know just enough about SQL server to be dangerous so I would like to do this using VB.net
As an example.
If we backed up the db monthly and renamed them. then would it later be possible to run querry's that spanned the db's.
1. Is this a doable concept?
2. Would I?
"Drop" the working db
Copy all the data from the working db to a new db
Purge the tables in the working db
Just looking for some conceptual help here to make a plan to do this. Any help or guidence you could give be would be greatly appreciated.
tia
Rafone
Statistics are like bikini's...
What they reveal is astonishing ...
But what they hide is vital ...
modified on Tuesday, August 26, 2008 10:35 AM
|
|
|
|
|
If you are using SQL Server 2005 (or 2008) have a read about partitioned tables, this may be the answer to your performance problems.
Whatever you decide, DON'T try and do it in VB.net, you could well end up losing all your data!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You could use Replication to copy your working DB to another server and use that for all querying. SQL Server offers good replication capabilities for offline processing.
|
|
|
|
|
Helo,
Can anybody help me!
I have 3 tables
first 1 have a header table with one row
the second table are all the data records
and the third is a trailer table!
So now i want to create 1 file
with all the fields of the header table separated through ;
then new line, the data table with the fields separated through ;
and finally the trailer table with fields separated through ;
i think i can do this with Bcp utility or something like this,
but i don't know how!
Please help!
Thank you
|
|
|
|
|
You can create a SQL statement giving you correct result set and then save the result to a file.
Use UNION and concatenate all fields from a table to a single field using desired separator
Mika
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
