Re: sql server (multplie column value as one row value) - Database Discussion Boards

hi,

thanks for the help.

Vijay V.

Yash Softech

Hi I hav got a stored procedure inside which im fetrching data from four tables..I want to genrate an excel file Populated with these results in the Stored procedure itself
Is it Possible? If yes then how can i do that?

Thnx and Regards

Joe

No, it is not...
You need to do this at the application level.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

You can write a DTS (or SSIS if using SQL Server 2005 or above) to run the stored procedure and export the results to an Excel file

====================================
Transvestites - Roberts in Disguise!
====================================

How about starting from Excel and pulling the data from the database into Excel ?

In Excel, go to the Data menu, Import External Data and use New Database Query to run the necessary query in your database.

Just a thought.
Smile | :)

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)

If you're using Express edition, SSMSE is a separate download. Check Microsoft SQL Server Management Studio Express Service Pack 2[^]

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