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 follows

COURSE ID      SKILL TYPE 

 21                        2

22                        1

23                        4

24                        1

IN SHORT:  I want to count the number of particular skill types and display them against their respective course id. You need to display one column data of database into two columns as output.

 Hope you people will help me in this regard.

Thanks in advance

RameshRaj
 

0
su
3/27/2008 11:59:23 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
610 Views

Similar Articles

[PageSpeed] 40

select case courseid when 1 then 'Primary Skill'  when 2 then 'Secondary Skill' else 'xxxSkill' end,count(*) from tablename group by courseid

---------------------------------------------------------------------------------------

Please click Mark As Answer if this helped in solving your problem.


Please click "Mark As Answer" if this hepled in solving your problem.
0
sudipta
3/27/2008 1:49:13 PM

select CourseId,(select count(*) from Table1 a where a.CourSeId = r.CourSeId and SkillType = 1) as primaryskilled,
(select count(*) from Table1 a where a.CourSeId = r.CourSeId and SkillType = 2) as secondaryskilled
from Table1 r
group by CourseId


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
3/27/2008 2:14:48 PM

Below query should resolve your problem.....

SELECT CourseID, COUNT(SkillTypeID) SkillCount, 'Primary' SkillType
	FROM EmpSkill
GROUP BY CourseID, SkillTypeID
HAVING SkillTypeID = 1
UNION
SELECT CourseID, COUNT(SkillTypeID) SkillCount, 'Secondary' SkillType
	FROM EmpSkill
GROUP BY CourseID, SkillTypeID
HAVING SkillTypeID = 2

Please, let me know, if this is not you are exactly looking for.

0
teribium
3/27/2008 2:54:44 PM

SELECT SUM(CASE WHEN SkillTypeID=1 THEN 1 ELSE 0 END ) as PrimarySkillTypeCount,

SUM(CASE WHEN SkillTypeID=2 THEN 1 ELSE 0 END ) as SecondarySkillTypeCount, CourseID

FROM empskill

GROUP BY CourseID


Limno

0
limno
3/27/2008 3:43:27 PM

 thanks a lot limno your query is working great....keep it up

 

have a nice day

Ramesh
 

0
su
3/31/2008 6:01:06 AM
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 ...

Query split column in 2 columns in SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1                Col2 BMW               FordMercedes         Audi So i can bound a table directly to that column!Is this p...

How to combine two columns into one in an sql
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls. Here is the stored Proc: SELECT DATEPART(mm, CALLSTARTTIME) ,  DATEPART(dd, CALLSTARTTIME), COUNT(*) FROM CALL_LOG_MASTER WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL) GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) It returns a table: MONTH   DATE   TOTAL NUMBER OF CALLS=======   =====   ===========1      ...

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

Connect to two databases, one SQL query.
Hi!Feels like I'm hitting my head against a brick wall with this problem. I have setup two connection strings to two databases and setup the relevant information in the web.config file to point to the databases.  string cs2 = ConfigurationManager.ConnectionStrings["ContractConnectionString"].ConnectionString; string cs = ConfigurationManager.ConnectionStrings["IPRConnectionString"].ConnectionString; conn = new SqlConnection(cs); conn2 = new SqlConnection(cs2);  I then have my SqlCommand comm = new SqlCommand("SELEC...

SQL query returns 'No column name' as a column heading
 hi,I'm trying to execute this query in SQL Server 2005 Express Edition, the problem is in the column heading of the result for the majorName fieldSELECT studentID,fname,mname,lname,familyName,gpa ,(SELECT majorName AS major FROM major WHERE majorID=(SELECT majorID FROM studentmajorWHERE studentID='0000000'))FROM student WHERE studentID='0000000'; Why the result of this query returns 'No column name' as a label for the majorName field? I use the AS clause to allows the column to be named, the column heading of the result table still 'No column name&...

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

is it possible to turn 1 column,many rows into one row,many columns in sql ?
 Hi AllI have the following query:Select CategoryName From Category Where CategoryId In(Select SkillId From SpecialitySkills Where CopywriterId = 13) It gives me the correct results but not in the form that i want, i would like it to be in one row, many columns. its in 1 column, many rows.Any ideas?  You must be talking about displaying it on a web page? Just create a loop that runs through the rows of the data that is returned then just make html table:dim tablestring as string = "<table><tr>" for i as integer = 0 to dt.rows.count - 1 tablestring += &...

How to Combine two column in one table using SQL statement ?
Could you write the simple SQL statement from 'Combine two column in one table '? I try to use 'Union' which combine two column in two table . thx You can use the concatention operator (+): SELECT column1 + column2 AS myColumn FROM myTable TerriTerri MortonEngagement Manager, NeudesicHow to ask a question If the columns are numeric then "+" will sum the column values. If you do not desire this, you should use str() function Check the below statement, select ltrim(str(column1)) + ltrim(str(column2)) from mytable http://www.eral...

Need query to get distinct column name and details based on that column using sql server 2000
hi, iam having three tables as shown below table 1                                                      table 2                                     &...

Populating drop down list with data from more than one column in an SQL database table
It goes like this. I have a "technician" table in my database. It has Fname, MI, Lname, and trade fields. I woul like to populate a drop down list called assigned tech with Lname comma Fname in the list. I can get any one column in the list but I would like to have "lastname, firstname" format in my drop down list. Can I do it with the fields in the database table, or do I need to make another field in the table called something like "fullname" with the info I want to show up in my drop down list? I am using C#. Thanks Jeremy This should help: ...

Query SQL DateTime column using ADO.NET Entities Framework
I have a SQL table called Slots with a Datetime column named Date.  I would like to query the db and return all rows where the date is equal to a date provided by the user on an asp.net form.  I have tried various ways to covert the datatime column, but always get an exception.  Any suggestions how to query a SQL datetime field where the date column is equal to a date specified by the user?    using (DBEntities1 myDB  new DBEntities1())   {      try        {  string querySt...

SQL query returns data, .NET returns #42S22Unknown column binding to dataset
I have a webservice with a common function which calls MYSQL and returns a dataset. Today I encounterd a strange error when performing a Select with a count() in it which I cant figure out. From a command line, the SQL query works fine and returns 9 rows... but VB.NET bombs with an error when filling the data adapter with the returned data. Please see my sample code below, is there an additional flag I need to call to make this work with VB? SQL Command Line: ( Works Great )mysql> Select taction, count(*) from kiosklogs where packageid='NewHi-Def-Test' group by taction;...

Web resources about - SQL query to one column od database into two columns - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

Resources last updated: 12/6/2015 5:02:15 AM