Multiple rows of data into 1 row results?

Is there a way to get multiple rows of data to show in 1 row of results?

I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.
I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).
Thanks!
0
bquinn7186
12/10/2003 9:27:36 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
609 Views

Similar Articles

[PageSpeed] 43

What you are trying to do is typically called a pivot table or cross tab query.

Take a look at this post: view post 350666. Someone has posted a suggestion to use a table control to accomplish this. See if anything there helps you. If not, post again :-)
Terri
Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
12/11/2003 2:33:00 AM
I think I may have to do it outside of SQL. I don't want to do any sort of aggregation of the data - just display it in 1 row instead of multiple and I don't see anywhere how to do the SQL cross tab without using an aggregate function of some sort.
0
bquinn7186
12/11/2003 12:55:16 PM
Well, you could write a UDF that would take the DataID as a parameter and would return a comma-delimited list of all of the facilities for that DataID.  Like this:


CREATE FUNCTION getFacilities
(@DataID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @facilities VARCHAR(8000)
SET @facilities = ''

SELECT
@facilities = @facilities + ', ' + FacilityTable.FacilityName
FROM
DataTable
INNER JOIN
FacilityKeyTable ON DataTable.DataID = FacilityKeyTable.KeyDataID
INNER JOIN
FacilityTable ON FacilityKeyTable.KeyFacilityID = FacilityTable.FacilityID
WHERE
DataTable.DataID = @DataID
IF @facilities <> ''
BEGIN
SET @facilities = SUBSTRING(@facilities,3,LEN(@facilities)-2)
END
RETURN (@facilities)
END

You would use that function like this:
SELECT DataID, dbo.getFacilities(DataID) FROM DataTable

Terri
Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
12/11/2003 4:46:28 PM
Thank you! That is exactly what I was looking for!
0
bquinn7186
12/12/2003 4:42:48 PM
If efficiency is a priority for you then this function is a poor solution since under the hood it means you are doing N queries for each row returned where N is the number of facilities.  A cross-tab query will be more efficient.
0
expostfacto
12/12/2003 7:27:26 PM
Reply:

Similar Artilces:

Arranging data on multiple rows into a sigle row (converting rows into columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on...

SQL Data displayed row by row
Hi,  I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data in row"  Any help would be appreciated  Thanks, Michael   mstoten: I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data i...

Several rows with the same data into 1 row
There is table where some rows includes the same data (at least UserId, Name but ID - unique) ID, UserID, Name, Money Required, Money Spent, Products 1    234        aaa     2000                    345                Product1 2    234        aaa     300      ...

How do I display data from an sql query row by row?
I have the following code (at bottom of msg) in a page I'm trying to write. It works ok with a datagrid but I don't want a datagrid on the page as they look ugly (by default). Questions ... 1. Can I use a datagrid to display data with a custom layout? 2. How can I access data obtained from an sql server stored procedure row by row? I'm told you do it with a Dataset but I don't know how. The site is to look like this ... http://www.numbersix.co.nz/temp/intranet.jpg ... that's the existing site in ASP but must be converted to ASP.NET to keep up with things. Can anyone hel...

Why does it not let me type data in my database fields and gives me error "No row was updated, the data in row 1 was not committed?
Hi, I'm typing example records in my database fields for username, last name, firstname, city, state, etc directly into the database field but when I try to save it it says:  No row was updated. The data in row 1 was not committed.Error Source: .NetSqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated.Correct the errors and retry or press ESC to cancel the change(s) Basically I'm just typing data into the fields but it won't allow me to save them.    The data types are all saved as...

Multiple grid rows per Data row in GridView
I have a dataset that is binded to a gridview. The dataset has 5 fields and a description. I want to display 1 row for the 5 short fields and 1 row underneath it for the description. (per record)   For example: ID Name  Surname Field3 Field 4 1   John     Smith      blah    blah     This is the long description for  John Smith spanning 5 cells 2   Peter    Parker     blah    blah     This is t...

Retrieve data row by row and multiple SELECT statements.
hai to all experts out there,can somebody help me please! I would like to do this sql select statement in vb.net in order to display the output in a gridview. I have a table name TBL_DOC consist of ID,DOC_NO,PIC_FLAG,MPIC1,MPIC2,MPIC3,MPIC4 ,MPIC5 columns. My intention is first i want to retrieve PIC_FLAG column data from all rows in TBL_DOC table  row by row like this: SELECT PIC_FLAG FROM TBL_DOC and then if PIC_FLAG = 'MPIC1' i want to do another select statement like this in the same table: SELECT ID,DOC_NO,PIC_FLAG,MPIC1 FROM TBL_DOC where PIC_FLAG = 'MPIC1...

ADO.net problem: i can not view image data for the rest of my table rows except the image for the first row but i can read all the other non image fields for all rows.
Hi i have a table with the following fields imgid............int imgdata........image imgtitle.........varchar(max) imgType........varchar(max) imgLength.....bigint incident_id.....int i have no problem with uploading the image but viewing the uploaded image(s) in a gridview has caused me great pain. The probelm is i have two pages. page1 grabs the image to display and page2 has a gridview that displays the grabed image. I wanted to have an sql statement like SELECT * FROM mytable WHERE incident_id=someValue. IF i have 5 records in this table and i view page2 ...

add data from multiple rows in table to one row in gridview
 heya is there any way of adding data from multiple rows in table to one row in gridview. eg. ipc                 column 1 ,            2 ,                       3,                 4  1,    11111                 33                  &nbs...

Data entry page. Multiple rows, ability to edit rows.
I'm working on creating a data entry page that allows the user to input as many records as they want; view them all, edit them, or delete them. In my mind, I picture a set of fields at the top of the page where they enter the data and a table/datagrid/gridview displaying the records they have entered right below that with buttons for editing or deleting. Then before they can submit the records to be written to the database I need to run some error checking. For instance they could be entering a number of transactions, positive and negative integer values, and when add...

Gridview row 0 selected, but data for row+1 is displayed... ??
Hello. Here's a rather interesting dilemma--hopefully easy to solve? Though I'm stumped for the moment, so any help will be greatly appreciated. I have a gridview with master records listed. The records are acquired using L2S and a sproc that applies Row_Number() to the results for custom paging. The resultset looks fine. It binds to the gridview as expected (displaying data for rows 1 through 5). Each master record has a "select" column to allow the user to select which master record to display in the detail record view. When select is clicked, the child records are&...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

Can you display data from a single row in multiple rows using a GridView?
I am currently displaying 3 fields in a gridview and it looks something like this:First Name Last Name Age ======== ======== === Chris Messineo 40 John Smith 35 Tim Jones 30I need to modify the gridview and display a bio, but I want it to display on 1 full line below each line, like this:First Name Last Name Age ======== ======== === Chris Messineo 40 Chris was born in a log cabin... John Smith 35 At the age of 10 John invented... Tim Jones 30 Mr. Jones was always a curio...

Data Controls insert rows into gridview then insert rows from gridview into SQL Table
Hello there, I'm still new at performing miracles with code . Currently I am having trouble with my latest coding adventure I have a web application which records jobs and labour assigned to jobs. The issue in particular I'm having is the way in which labour is to be assigned. I want to use standard controls (Textbox, Dropdownlist) to record the values into a gridview (ie,put multiple rows into the gridview using the controls). Then, when all the inserting is complete, the user clicks a submit button to insert all the rows from the gridview into a database table. The reason for...

Web resources about - Multiple rows of data into 1 row results? - asp.net.sql-datasource

Code division multiple access - Wikipedia, the free encyclopedia
Code division multiple access ( CDMA ) is a channel access method used by various radio communication technologies. It should not be confused ...

Sydney Trains missed multiple chances to avoid Edgecliff derailment, says ATSB report
... has highlighted concerns about a focus on a &quot;can-do culture&quot; rather than safety. Sydney Trains staff and senior managers missed multiple ...

Apple Needs to Offer Own TV Content after Multiple ‘Speed Bumps,’ Says FBR
FBR & Co. analyst Daniel Ives this morning reflects on reports by Bloomberg and others that Apple ( AAPL ) has shelved an effort to create a ...

Google Ventures reportedly closed its European fund after multiple stalled deals
Earlier this week, Google Ventures announced it was closing down its dedicated European fund . It had been assigned $125 million to invest, but ...

Multiple McDonald’s Locations Forced To Close After Prank Callers Convince Workers To Test Fire System ...
When I think of prank calls, I conjure up images of teenage girls huddled around their clear plastic phones, calling boys in their class and ...

OKC Police Officer Found Guilty On Multiple Counts Of Raping Black Women
Former Oklahoma City Police officer Daniel Holtzclaw will be going to prison for a very, very long time. After deliberating for well over 45 ...

Cowboys Fan Beats Up Multiple Attackers In Parking Lot Brawl
That's the best defense anyone wearing a Tony Romo jersey has ever played

The Philadelphia 76ers announced that they suspended Jahlil Okafor—he of the multiple fights outside
The Philadelphia 76ers announced that they suspended Jahlil Okafor—he of the multiple fights outside of bars and one of their only competent ...

Google's Chromecast Audio beams music to multiple rooms
If you're been eyeing Google's Chromecast Audio for your holiday shopping spree, Mountain View just added some new features to sweeten the deal. ...

China Mobile Ltd. (ADR) on Focus After Forming Bearish Multiple Bottom Chart ...
China Mobile Ltd. (ADR) on Focus After Forming Bearish Multiple Bottom Chart ... FinancialMagazin.com The stock of China Mobile Ltd. (ADR) ...

Resources last updated: 12/11/2015 4:38:42 PM