Display Multiple Columns into One Column

My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset.

SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2

FROM Hardware INNER JOIN

[User] ON Hardware.UserID = [User].UserID INNER JOIN

Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN

MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN

PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN

PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID

This outputs:-

Asset number     FullName     Model     Expr1     Expr2

00000                User Name   Model     NULL      NULL

00001                User Name   NULL      Model     NULL

00002                User Name   NULL      NULL      Model

However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:-

Asset number     FullName     Model

00000                User Name   Model

Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page?

Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition

Thank you for your replies


Rhys
0
Rhys
2/5/2008 11:09:33 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
1092 Views

Similar Articles

[PageSpeed] 31
Get it on Google Play
Get it on Apple App Store

I ma providing the updated Top part of the query  

SELECT Hardware.AssetNo, [User].FullName,
isnull(MonitorModel.Model, '') + isnull(PCModel.Model,'') + isnull(PrinterModel.Model ,'') as 'Model'


Vikram
www.vikramlakhotia.com


Please mark the answer if it helped you
0
vik20000in
2/5/2008 11:36:29 AM

SELECT Hardware.AssetNo, [User].FullName, ISNULL(MonitorModel.Model,'') + ' ' + ISNULL(PCModel.Model,'') + ' ' + ISNULL(PrinterModel.Model,'') AS 'Model'

FROM Hardware INNER JOIN

[User] ON Hardware.UserID = [User].UserID INNER JOIN

Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN

MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN

PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN

PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID

 


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
2/5/2008 11:36:55 AM

If I understand your model right you could use UNION for this purpose, i.e.:

 

SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model

FROM Hardware INNER JOIN

[User] ON Hardware.UserID = [User].UserID INNER JOIN

Model ON Hardware.ModelID = Model.ModelID INNER JOIN

MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID 

UNION ALL 

SELECT Hardware.AssetNo, [User].FullName, PCModel.Model

FROM Hardware INNER JOIN

[User] ON Hardware.UserID = [User].UserID INNER JOIN

Model ON Hardware.ModelID = Model.ModelID INNER JOIN

PCModel ON Model.PCModelID = PCModel.PCModelID

UNION ALL 

SELECT Hardware.AssetNo, [User].FullName, PrinterModel.Model

FROM Hardware INNER JOIN

[User] ON Hardware.UserID = [User].UserID INNER JOIN

Model ON Hardware.ModelID = Model.ModelID INNER JOIN

PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID

 

But consider also merging three Model tables into one with adding one discriminator value field (i.e. ModelType). You can provide your database model if you need more help with optimizing/normalizing.


Best regards,
Thomas

------------------------------------
http://www.nconstruct.com
------------------------------------
0
ThomasT
2/5/2008 11:46:49 AM

Thank you everyone for your replies they all produce the result i want to display, but have decided to use the COALESCE function as it suits my needs better. Code below.

SELECT     Hardware.AssetNo, [User].FullName, COALESCE (PCMake.Make, MonitorMake.Make, PrinterMake.Make, ScanMake.Make, CameraMake.Make,
                      ProjMake.Make, InWhiteMake.Make) AS Make, COALESCE (PCModel.Model, MonitorModel.Model, PrinterModel.Model, ScannerModel.Model,
                      CameraModel.Model, ProjModel.Model, InWhiteModel.Model) AS Model, Type.Type
FROM         Hardware INNER JOIN
                      [User] ON Hardware.UserID = [User].UserID INNER JOIN
                      Model ON Hardware.ModelID = Model.ModelID INNER JOIN
                      Type ON Model.TypeID = Type.TypeID LEFT OUTER JOIN
                      CameraModel ON Model.CameraModelID = CameraModel.CameraModelID LEFT OUTER JOIN
                      CameraMake ON CameraModel.CameraMakeID = CameraMake.CameraMakeID LEFT OUTER JOIN
                      InWhiteModel ON Model.InWhiteModelID = InWhiteModel.InWhiteModelID LEFT OUTER JOIN
                      InWhiteMake ON InWhiteModel.InWhiteMakeID = InWhiteMake.InWhiteMakeID LEFT OUTER JOIN
                      MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN
                      MonitorMake ON MonitorModel.MonitorMakeID = MonitorMake.MonitorMakeID LEFT OUTER JOIN
                      PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN
                      PCMake ON PCModel.PCMakeID = PCMake.PCMakeID LEFT OUTER JOIN
                      PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID LEFT OUTER JOIN
                      PrinterMake ON PrinterModel.PrinterMakeID = PrinterMake.PrinterMakeID LEFT OUTER JOIN
                      ProjModel ON Model.ProjModelID = ProjModel.ProjModelID LEFT OUTER JOIN
                      ProjMake ON ProjModel.ProjMakeID = ProjMake.ProjMakeID LEFT OUTER JOIN
                      ScannerModel ON Model.ScannerModelID = ScannerModel.ScannerModelID LEFT OUTER JOIN
                      ScanMake ON ScannerModel.ScanMakeID = ScanMake.ScanMakeID
WHERE     (Model.TypeID <> '4')


Rhys
0
Rhys
2/6/2008 8:45:57 AM
Reply:

Similar Artilces:

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

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 ...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

Display Multiple Columns from the Database in One Column of a Table in the Form
Hi, I have a table with several columns in the database and I want to display this some of these cols in one column. So for example: I have in the table, NameCustomer, TitleCustomer, and DescriptionCustomer and I want to display this info in one col of a table, just like that: NameCustomer TitleCustomer DescriptionCustomer Those three in one col, I do not know if can use a datagrid or something else, Does any body has a suggestion?  Hi Use a repeater to populate your columns. Thanks  Wonder is the beginning of wisdom[ If my p...

Split One Column into multiple columns
Hi all, I have a requirement like this  , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)   Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469   How can i acheive this one with out data lose in Address Column. Thanks in advance.      G.JaganMohanRaoMumbaiIndia If t...

Display Key columns and all columns for tables in one diagram
In Tools->Display Option-> Object View ->Table, you could choose to display only key columns, PK columns, top X columns or all columns for tables. But it applies to the whole diagram. I wonder whether we could do it on table level, say to display all columns for fact table but just PK columns for dimension tables so we could squeeze one subject area in one page. Thx, Richard Mei BMO Financial Groups Manually drag up the bottom of the table outline to show just the columns you want. -- Mike Nicewarner [TeamSybase] http://www.datamodel.org mike@nospam!datamodel...

Displaying single column of data across multiple columns..
Hi all... Okies my problem.... I have a dataset that has a single column, with 100 rows. I wan't to display this in a table with 4 columns with 25 rows in each column.. Does anyone know of a simple solution? Thank's in advance. Jeremy Baka Saru Hi, you can try the DataList with RepeatColumns property set to 4.  Alvin ChooiMicrosoft ASP.NET™ Enthusiast v1.1 / v2.0, Malaysia Blog : http://alvinzc.blogspot.com...

displaying multiple columns in a gridview populated from a single column
hi, i'm a newbie so my apologies in advance if i am not explaining my problem clearly... i'm happy to repost to help clarify anything. Problem: i am not able to sort and organize my record set the way i need to to popluate my gridview. i am looking for suggestion on either how to construct my sql statement, or manipulate my gridview. Environment: i am working in asp.net 2.0 and using a sql server db. Details: I have a single table called Loans. i need to retrieve three columns from that table: LoanOfficer, LoanState, and LoanKey. The LoanState column contains one of three v...

Displaying a single Data Window Column in multiple columns
I have a datawindow that is retrieving a single column. Using Powerbuilder 5.04, is there anyway to display this data in multiple columns sorted (alphabeticlly) vertically (eliminates n-up). Any help would be appreciated. Thanks Yehuda Newspaper columns? Yehuda wrote in message <3ACB5511.E9262E3C@yahoo.com>... >I have a datawindow that is retrieving a single column. Using >Powerbuilder 5.04, is there anyway to display this data in multiple >columns sorted (alphabeticlly) vertically (eliminates n-up). Any help >would be appreciated. >Thanks >Yehuda &...

SQL query to one column od database into two columns
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now  SkillTypeID column has data 1 or 2 .......in here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID      SKILL TYPE  21                        222  &n...

When is a column not a column?
I have several extremely similar datawindows that I have to maintain that go against the same table. Recently, I had to add a column to that table, and also add the column to ALL of these datawindows that go against this table. I added the column to the first datawindow, set up the edit control style, etc. I then did a copy/paste of the columns edit control to the remaining dw's (of course remembering to first add the column in SQL). Everything (update props, column specs, column props) seems to indicate that the pasted column is linked to the column I selected, HOWEVER, when I...

[*-)]how to delete all columns except one column for the corresponding column name......?
hi friends,          i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me......  ---------------------------------------------------Don't forget to "Mark as Answer" if you are satisfied. Use the following  DataTable.Columns.Rem...

how to pull several columns info from db then display them as one column in datagrid?
I have difficulty to gether several columns info from db to display them as one column in datagrid. say I have a table which has columns: "name", "street", "address", "city", "state", "zip", "status", Ex: I want to pull "street address", "city", "State", and "zip" from that table then diaplay them as one column "Address" in the datagrid so that the datagrid will only have 3 columns to display --"Name", "Address" and "Status". How do I do it...

Filter one datasource column with multiple values
I've done some poking around, but didn't see an answer on this. I have a GridDataSource that I want to filter when the page loads. But I want to filter a column by multiple values, like using a "contains" or an "in". The column is an integer value. I tried this: protected void GridDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e) { if (someCondition) { using (MyDBContext db = new MyDBContext ()) { e.Result = db.Employees.Where(emp => _currentUser.ShiftIds.Contains(emp.ShiftID)); } ...

Web resources about - Display Multiple Columns into One Column - 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 ...

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 ...

Multiple Victims In San Bernadino Mass Shooting; 1-3 Shooters Sought
When is it enough to actually do something? Fuck the NRA. Fuck the Republican party and the right wing hate machine. Those Syrian refugees might ...

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 ...

Bluestacks now runs multiple Android apps on Windows simultaneously
Bluestacks has come a long way since its original app player in 2011, which was notable for being one of the first methods for running Android ...

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) ...

Ohio Teen convicted for multiple swatting attempts
A Painesville, Ohio teen has been sentenced to five years in a juvenile correctional facility this week for calling in multiple fake emergencies ...

Resources last updated: 12/10/2015 12:42:08 PM