SQL Query To One Column Od Database Into Two Columns
Mar 27, 2008
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
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 possible with SQL, and how can i build it.Thanks.
I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.
For (hypothetical) example:
There is an existing table with following info in three columns: userid record recordtag 1 joe 1 1 j 2 1 jr 3 2 bob 1 2 a 2 2 sr 3 where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)
I need to query these records for a report so it the output is:
userID firstname middleinitial suffix 1 joe j jr 2 bob a sr
What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100 SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security] FROM HS140_Rpt_Tmp_ForSummary s WHERE s.[Security] = 'Yes' GROUP BY Market
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
On my company site, I have created a database that is for the purpose of tracking google adwords, as well as pages that the user visits. For instance, if you were to search for "guitars" and then click our ad, an entry is created in the database like thisKeyword: SessionGUID: PageVisited: VisitedDateTime:Guitars lkjfeilfjskdlfjsije ~ViewCategory.aspx?Cat=Guitars,KW=Guitars 12/01/2000 12:00amGuitars lkjfeilfjskdlfjsije ~ViewProduct.aspx?ProductID=1253&SubProductID=3 12/01/2000 12:03amGuitars lkjfeilfjskdlfjsije ~Search.aspx?Q=BC%20%Rich 12/01/2000 12:05am Pretty much, in our administrative area, I want to be able to have a table that would generate these results: Keyword Total HitsGuitars 3So im guessing that obviously I would need to do a select distinct for the Keyword column, but how do I also have a column showing a count of the records?
I am having trouble trying to filter my sql database using the query string against a date column in my database. I set a date variable = "dd/mm/yyy" format but when I apply that in the sql query as:
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB 01/01/11 100 5 02/01/11 102 4
Share Price....
Date CompA CompB 01/01/11 100 100 02/01/11 101 99
Event Data...
Date Company 01/01/11 CompA 02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance: SELECT Event.Date, Event.Company FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column WHERE Column = Event.Company AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
Hi all, In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table: dbo.LabTests Column_name Type Length Prec Scale AnalyteID int 4 10 0 (Primary Key) AnalyteName nvarch 510 CasNumber nvarch 510 Result numeric 5 8 2 Unit nvarch 510 SampleID int 4 10 0 (Foreign Key)
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key encryption by password = 'testAppleA3';
create certificate test with subject = 'test certificate', EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000')); insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees --delete from employees select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test drop certificate test drop master key
Then recreate the master key:
create master key encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
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......
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
Code Block WITH AwaitingApprovalCTE AS ( SELECT type = 'Members Awaiting Approval Yesterday' , borrowers = (select count(*) from LoanApplication where SubmittedOn >= GETDATE()-1) , depositors = (select count(*) from CDOrder where SubmittedOn >= GETDATE()-1) , MemberStatus = CuStatus from Member where Custatus = 'Pending'
),
AcceptedCTE AS ( SELECT type = 'Members Accepted Yesterday' , borrowers = (select count(*) from LoanApplication where SubmittedOn >= GETDATE()-1) ,depositors = (select count(*) from CDOrder where SubmittedOn >= GETDATE()-1) , MemberStatus = CuStatus from Member where Custatus = 'Accepted' ), RejectedCTE AS ( SELECT type = 'Members Rejected Yesterday' , borrowers = (select count(*) from LoanApplication where SubmittedOn >= GETDATE()-1) , depositors = (select count(*) from CDOrder where SubmittedOn >= GETDATE()-1) , MemberStatus = CuStatus from Member where Custatus = 'Denied' ),
combinedCTE AS ( SELECT * FROM AwaitingApprovalCTE UNION ALL SELECT * FROM AcceptedCTE UNION ALL SELECT * FROM RejectedCTE
) SELECT type, NoOfMembersJoined = borrowers + depositors FROM combinedCTE
Problem is I get only one field. I get only the first one.i.e. "Members Awaiting Apporval Yesterday" I dont get other two types.."Members Accepted Yesterday" and "Members Rejected Yesterday". Final report should look like this:
Type NoOfMembersJoined
Members Awaiting Approval yesterday 69 Members Accepted Yesterday 15 Members Rejected Yesterday 31
But I get only the "Members Awaiting Approval yesterday" for all the rows.
Type NoOfMembersJoined
Members Awaiting Approval yesterday 0 Members Awaiting Approval yesterday 0 Members Awaiting Approval yesterday 0 Members Awaiting Approval yesterday 0 Members Awaiting Approval yesterday 0
NoOfMembersJoined = 0 is ok because there may not be any data yet.
I can not figure it out why it is. Pease can anyone tell me where I went wrong?
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID in Employee table. Can any one help? Thanks
Hi, I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
SELECT E1.id, E2.id FROM productHeadings AS B1, productHeadings AS E1, productHeadings AS E2 WHERE B1.id = E1.parent_id AND E1.id = E2.parent_id and B1.id = 1
the result is 2 id columns... is there a way to return all id in 1 column?!
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta". The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester". Data for columns "Activity" and "Hours Used" taken from table called ALD. "Delta" column is the difference between Hours Given and Hours Used. For "Hours Given" in table "Daily", the data source should change every 12 hours. For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
Hey ya'll... I need to create a query that I can run that will allow me to essentially take the first initial and last name and combine them from two columns in one table and update one column with that new data in another table. Example of need: UPDATE tblLogin.UserName with (first character(tblEmployee.FName)+(tblEmployee.LName)) WHERE tblLogin.EmployeeID = tblEmployee.EmployeeID. That was TOTALLY pseudo code and I know I'll need a join statement in there somewhere. Both tables reside on the same database, such as this: dbEMPLOYEE (Database)--> tblLogin (Table)----> UserName (Column)--> tblEmployee (Table)----> FName (Column)----> LName (Column) Any help would be GREATLY appreciated! Hopefully something soon - this was handed to me a few minutes ago and I have a few minutes to get it done.iSheahan
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.
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
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
