I want to convert the row value as column name example:--> value ratio 3 4.166666666661 1.315789473680 00 0 To :-> value ratio ratio1 ratio2 ratio3 3 4.166 1.315 0 0 any ideas?
I need to convert the number of rows returned from a column in a table & assign it to a variable, so I can use it to control a loop. So far no joy. The T-SQL used is :
SELECT COUNT(column_name) FROM table AS column_alias GO SET @numberofrows = column_alias GO
Any suggestions? I have declared all variables etc. The headache seems to be the converting from the column alias.
Assuming I have the following data where the header represents budget cost and the value represent no. of projects
<2K 2K-5K >5K -------------------- 10 15 5
For the above table: the following is my sql:
SELECT
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL<2000000 THEN 1 ELSE 0 END) <2K, SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 THEN 1 ELSE 0 END) 2K-5K, SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=5000000 THEN 1 ElSE 0 END) >5K
FROM
PRJ_PROJECTS AS PRJ
How do I program it in sql such that the data will be displayed as below? Thanks
Hi All,I need to help with converting rows to columns in SQL2k.Input:Id Name Role58Ron Doe Associate58Mark BonasDoctor59Mike JohnsonDoctor59John SmithAssociate102Chris CarterAssociate102Ron Doe Associate102James JonesAssociateOutput should look like:IdDoctorAssoc1Assoc2Assoc358Mark BonasRon Doe NULLNULL59Mike JohnsonJohn SmithNULLNULL102NULLChris CarterRon Doe James JonesThere could be more than 3 associates in the input but I only need 3above columns for associates.I used following query:SELECT Q.sales_id,doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'and Q2.sales_id = Q.sales_id),assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id)FROM view1 QGROUP BY sales_idand I get this error "Subquery returned more than 1 value" since thereare multiple associate for Id 102.Thenks
I have a table (Not my own, cannot be changed) that has this structure:
Table1 ID, field, data ----------------------- 1, fname, john 1, lname, doe 1, address, 123 any st 2, fname, jack 2, lname, sprat 2 address, 345 some st 2, phone, 321-555-1234
I want this:
Table2 ID, fname, lname, address, phone -------------------------------------------------- 1, john, doe, 123 any st,, 2, jack, sprat, 345 some st, 321-555-1234
Notes: This query will need to run on a regular schedule. There are over 100 fields, I don't want to do this 100 times:
1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100 5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98.1
Now I want to display the data in this format
ID Color Density Value ID Color Density Value ID Color Density Value ID Color Density Value 1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100 5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98
Basically I want this format so that I can bind this to the datagridview directly and then use the same for a two way binding (saving) back to the database. How do I get this result format using a query? Thanks in advance.
date item_id grade in out ------ ------- ------- ----- ----- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total ------- --- ---- --- --- ------- 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 -8 20 -10 2
I want result group by Item_id and sum of grade in column . where grade could be any Alphabet, the column of result query could varies. Each grade contains sum of (in-out) of item_id of detail table.
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int); insert t1 select 1, 'A', 100 union select 2, 'B', 120 union select 3, 'C', 140 union select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS (ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT ) INSERT INTO #TEST_RESULTS VALUES(1,'A','2015-01-01' , 0 ,1) ,(1,'A','2015-01-01' , 0 ,1) ,(1,'A','2015-01-01' , 1 ,3) ,(1,'A','2015-01-01' , 2 ,6)
I want to have a query like SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup
where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like: SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings ---------------- Com1, 9600, N, 8, 1 Com2, 2400, E, 7, 2
I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500 W 21-30 400 W 31-40 200 A 17-20 100 H 41-50 250 H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
i want to convert the rows of one table to be the column of another table in run time......i have two tables one having student id and name and in another table there are student id, marks and subject as english. hindi and maths in rows...i want to make another a third table which contains student id , name , marks as english, hindi, maths as the column....tried a lot but didnt get the right way...do we have to do it through Join or is there some other technique by which we can solve the query........i dont know wheather i am really informative or not..but am sitting online if incase you wanna know something else even..........
SELECT 'Type'[Type] ,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END ,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END FROM Table1 WHERE (Code BETWEEN '09' AND '12') GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4 Type 14022731.60 0.00 0.00 0.00 Type 0.00 4749072.19 0.00 0.00 Type 0.00 0.00 149214.04 0.00 Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50)) insert into #temp values(1,'test','head1') insert into #temp values(1,'test','head2') insert into #temp values(1,'test','head3') insert into #temp values(2,'test1','head1') insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead 1test *head1,*head2,*head3 2test1 *head1,*head2
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500 W 21-30 400 W 31-40 200 A 17-20 100 H 41-50 250 H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
Name Description Date Question Answer Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A Profile Assessment 01/01/2015 The total value of assets? Less than GBP 1 million
Customer A Profile Assessment 01/01/2015 The volume of transactions undertaken? Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name Description Date How complex is the structure? The total value of assets? The volume of transactions undertaken? How was the client introduced? Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
