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               B3

3               C4 

Gridview should look like,

No. Type A  Type B  Type C

1      1           2           -

2      2           2           -

3      -            3           4

 

So Something like I can have different query to display for each column but need to bind to multiple column of one gridview . I can use any other control Just need to display results .

 

 

 

 

 

0
pborad
11/6/2008 3:27:41 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1444 Views

Similar Articles

[PageSpeed] 33

Here is the Sql that makes this issue work

SELECT     CASE WHEN Typea.numNumber > 0 THEN typea.numnumber WHEN Typeb.numNumber > 0 THEN typeb.numnumber ELSE typec.numnumber END AS Expr1,
                       TYPEA.TypeA, TYPEB.TypeB, TYPEC.TypeC
FROM         (SELECT     numNumber, RIGHT(strType, 1) AS TypeC
                    FROM          aaa_Temp AS aaa_Temp_1
                    WHERE      (numNumber = CASE WHEN LEFT(strType, 1) = 'C' THEN numNumber ELSE 0 END)) AS TYPEC FULL OUTER JOIN
                   (SELECT     numNumber, RIGHT(strType, 1) AS TypeB
                    FROM          aaa_Temp AS aaa_Temp_2
                    WHERE      (numNumber = CASE WHEN LEFT(strType, 1) = 'B' THEN numNumber ELSE 0 END)) AS TYPEB ON TYPEC.numNumber = TYPEB.numNumber FULL OUTER JOIN
                   (SELECT     numNumber, RIGHT(strType, 1) AS TypeA
                    FROM          aaa_Temp
                    WHERE      (numNumber = CASE WHEN LEFT(strType, 1) = 'A' THEN numNumber ELSE 0 END)) AS TYPEA ON TYPEB.numNumber = TYPEA.numNumber

Although what you should really be doing is do a pivot table- which would handle any number of types. Use th Pivot SQL syntax to accomplish this. (which is what i manually did here)

The pivot table would allow you to send all the types to column headers, your No. would be a row header.

 

If you need any hints on how this will work let me know,

Good luck- Jeremy

0
iamhers
11/6/2008 4:58:31 PM

 

set nocount on;
use tempdb;
if object_id('dbo.t1') is not null drop table dbo.t1;

create table t1 (Num int,  TypeColumn char(2))
INSERT INTO t1
SELECT 1, 'A1'
UNION ALL SELECT 1, 'B2'
UNION ALL SELECT 2, 'A2'
UNION ALL SELECT 2, 'B2'
UNION ALL SELECT 3, 'B3'
UNION ALL SELECT 3, 'C4'


SELECT Num as [No.], ISNULL([A],'-') as [Type A], 
ISNULL([B],'-') as [Type B],
ISNULL([C],'-') as [Type C],
ISNULL([D],'-') as [Type D]
FROM (
SELECT Num, LEFT(TypeColumn,1) as Col, 
RIGHT(TypeColumn,1) as Val 
FROM t1 ) src
PIVOT (MAX(Val) FOR Col IN ([A], [B],[C],[D])) pvt
 
Limno

0
limno
11/6/2008 6:33:15 PM
Reply:

Similar Artilces:

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

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

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Using the gridview to display multiple joined tables, and then allow editing of all columns displayed?
I'm just curious if this is possible. I'm using a SqlDataSource to populate a gridview with data from multiple tables using a inner join select statement.It would be nice if it were possible to allow a user to click "Edit" and be able to update every single column for the particular row in every single table. Is it possible to do something like this?   Yes this is possible, you just have to use the proper Edit SQL command, that might be a little complex depending on how many DataTables you are editing.www.someguy.ca rantings from some Canadian guyFollow me on twitter as...

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

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

update multiple columns in one sql statements
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax. I have a table with transactions - with tran types as the key. in this example, types = A,B,C ,D. in this first example I am updating the sum of QTY to value t_A based on tran types =A. can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values. Create table #t_reconcile( t_lot_number int not null, t_A ...

SQL Multiple values from one column query?
Hello, Im trying to make a query that returns data like so:I have the following columns; Name, Description, Link, Year, Sketched, Colouredin which Year is an Int, Sketched and Coloured are boolean and the rest are varchar(xx). Now I want a query in which I can choose to get something with the following values: Year either 2007 or 2008 , Sketched either true or false and Coloured is true.I tried something like this: SELECT Name, Description, Link, Year, Sketched, Coloured FROM ImageTabe WHERE (Year = @Year1 OR Year = @Year2 OR ...

how to populate a datagrid columns from one or more SQL tables?
Hello Friend !,                     I would like to populate a datagrid columns from multiple SQL tables.If you know any link regarding my question please let me know.Much Obliged & Thanks a Lot,Myself Do the tables have relationships to one another and you want to select some columns from each table to populate the grid? OR You want to get data from the tables and just combine them to a row to display in the grid?DarmarkMark as Answer, if this reply answers your post. Hi Sidewinder2 ...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

How to manipulate images and combe multiple database columns into one column in a GridView control
Hi Folks,I'm hoping someone can help me out with something.I am trying to put together a photo album as a learning exercise and have a GridView control with two columns linked to an Access Data Source. In the left hand column (called Image) I want to display the photo and in the right hand column (called Details) I want to provide details such as Date, Location, People and Comments, Camera Type etc. Each of these is columns is taken from the database I have set up.However, I have am having some problems:1: At the moment I have been able to get the image to display (using an image column ...

Web resources about - Display multiple columns in Gridview from One Column of SQL table - asp.net.sql-datasource

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Multiple sources raise serious questions about Hillary Clinton's physical health
Breitbart News reports that John Cardillo, a former NYPD officer who provided VIP security details, knows two sources (a federal agent and an ...

Facebook is testing multiple topic-based News Feeds for mobile devices
... the information you want to see at the appropriate times. According to the screenshots taken by Jason Stein, Facebook looks to be testing multiple, ...

Multiple Jobholders Artificially Boost "Full-Time" Employment: Does the Sum of the Parts Equal the Whole ...
... pretty much confirms what I have said about Obamacare boosting part-time employment at the expense of full time jobs. Please consider Multiple ...

Munich Police: Islamic State Planned New Year’s Eve Attack with Multiple Suicide Bombers
Munich police warned of a “serious, imminent threat” by Islamic State group suicide bombers wanting to commit an attack on New Year’s Eve.

An insanely high percentage of people — about 42% — are seeing "Star Wars: The Force Awakens" multiple ...
With the enormous box office sales "Star Wars: The Force Awakens" is generating, it's a good bet that many people are going back numerous times ...

Miami teen shot multiple times at grandma's home
The victim reportedly remains in the hospital as police continue to search for the suspect

BREAKING: Tomah VA Mental Health Specialist JAILED Over Multiple Sexual Assault Allegations
BREAKING: Tomah VA Mental Health Specialist JAILED Over Multiple Sexual Assault Allegations

Facebook tests multiple News Feeds based on your interests
... yearning for a way to cut through the clutter that is your Facebook News Feed, you may soon be in luck. The social network is testing multiple ...

Best Healthcare Benefit Practices for a Growing Company With Multiple Locations
An important part of culture and values for many emerging businesses has been the benefit package they provide for the team. For many businesses, ...

Resources last updated: 1/11/2016 2:39:13 PM