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 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 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,
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
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.
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data. Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount 123, Grenadine, 100.00, 75.00, 15.50, 2.00 456, Lime Juice, 120.00, 81.00, 17.25, 2.00
What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.
I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.
The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.
I am all out of creative ideas, any help would be appreciated.
Hi, I have some data in one table. I need to get the data in the same table, into different rows. I mean to say that, I have data like InvNo InvAmt GstAmt 12 1900 79
and I need to get data like
InvNo Amt 12 1900 12 79
Can anyone help me as to how can i get data in this format.
Hi I have a stored proc that pulls in data, I have four columns that, where there is data I need to times by 100 but when empty to remain empty, currently where empty I end up with 0's
Do i need to do a convert or something? can someone help me out thanks
Code Snippet USE [WINDRUSHDAL] GO /****** Object: StoredProcedure [dbo].[spgiffordstelesalesdev2] Script Date: 02/28/2008 12:34:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[spgiffordstelesalesdev2] AS BEGIN SET NOCOUNT ON; SELECT BLCUSA AS Company, BLCUSB AS Account, BLDELN AS DeliverTo, blcnam AS [Account Name], BLCTS1 * 100 AS Monday, BLCTS2 * 100 AS Tuesday, BLCTS3 * 100 AS Wednesday, BLCTS4 * 100 AS Thursday, BLCTS5 * 100 AS Friday FROM LIVEAS400.S65C422B.WRFDTA.PARDADR AS PARDADR_1 WHERE (BLCUSA = '2') Union SELECT abCUSA AS Company, abCUSB AS Account, 0 AS DeliverTo, abcnam AS [Account Name], abCTS1 * 100 AS Monday, abCTS2 * 100 AS Tuesday, abCTS3 * 100 AS Wednesday, abCTS4 * 100 AS Thursday, abCTS5 * 100 AS Friday FROM LIVEAS400.S65C422B.WRFDTA.PARNADR AS PARNADR_1 WHERE (abCUSA = '2')
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.
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:
