Getting One Row From Multiple Rows
May 13, 2008
I have a Oracle table with columns like:
zone, plot, collection_start_date, collection_end_date, submit_date etc.
Each zone has multiple plots. Data collection is monthly but the start date can vary, depending on some factors.
For each zone and plot, there are multiple entries for the same month.
Here's what I need to do -
1) I need to comeup with one row for each zone, plot, month combination.
2) THe row should have the zone, plot, TO_CHAR(collection_start_date, 'MM') MONTH, TO_CHAR(collection_start_date, 'YYYY') YEAR, submit date, The difference between the submit_date and collection_start_date
The problem is multiple rows for the same month for a zone, plot and the collection_start_date varies for each row and i need to get the row based on this varied date field.
Any idea?
Thanks.
View 6 Replies
ADVERTISEMENT
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Dec 25, 2005
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.
View 1 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Oct 15, 2009
is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing
code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f
I wanted to update it to
code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y
how to do it?
View 5 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
May 4, 2001
Hi, Gurus,
I am trying to populate a table with repeating groups in multiple columns by using information from two other tables. The sample tables and records are like:
Table A
CSID
1
2
3
4
Table B
CP_IDCO_CODE
12C1
12C2
12C3
12C4
13C5
13C6
13C7
13C8
14C9
14C10
14C11
14C12
Table C (The empty table I want to populate like the following)
CSID CP_ID_1 CO_CODE_1 CP_ID_2 CO_CODE_2 CP_ID_3 CO_CODE_3
112C1 13 C514 C9
212C2 13 C614 C10
312C3 13 C714 C11
412C4 13 C814 C12
What is the best way to do it? Thanks in advance.
Sam
View 2 Replies
View Related
Sep 7, 2007
Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:
This is the how the table is:
EmpID Designation
678
CFA
679
CFA
680
CFA
685
CFP
685
CIMA
685
IMCA
I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA
could anyone provide some help on this?
Thanks
View 1 Replies
View Related
Jan 17, 2007
Hello I am wondering if this is possible.I have a two tables one contains Plan information and another thatcontains product information about the planex:Plan tablePlanID Plan_name1 a2 bProduct TableProductID PlanID Comments1 1 com12 1 com23 1 com3What I am looking to do if possible would be the followingPlan Product1 Comments1 Product2 Comments21 1 com1 2 com2I am wondering down what path I should explore cause I am new to this.I am using sql 2005
View 2 Replies
View Related
Mar 11, 2008
i have a table
View 1 Replies
View Related
Mar 17, 2008
How would I update a table where id = a list of ids?Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.example: 1,4,7,8 Any help much appreciatedBarry 1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
View 3 Replies
View Related
May 13, 2004
Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
View 2 Replies
View Related
Jul 30, 2004
Does anybody know how to insert multiple rows into a table with one query string(i'm using sqlServer)?
Thanx,
David
View 1 Replies
View Related
Jul 14, 2005
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID Primary Key TeamCode a code value representing different teams Week will equal to 2Points nullable valuee.g.Table 1======UID TeamCode Week Points1 A1 1 1002 A2 1 99trying to insert into table 1Table 1========UID TeamCode Week Points1 A1 1 1002 A2 1 993 A1 2 null4 A2 2 nulletc...As you can see, UID is primary key, TeamCode may repeat according to week value Week is a constant Points will be nullHow can I do that with a single Insert Command? Thank you for your help. :)
View 5 Replies
View Related
May 5, 2003
I inherited a database with Street, CrossStreet1 and CrossStreet2. I wish to read the row once and output 3 rows.
I know I can run 3 separate query's but why read through 1 million rows 3 times, if you can read through it once.
I can do this in SAS using the output statement.
Hoping someone knows how to do this in SQL.
:confused:
View 6 Replies
View Related
Feb 13, 2008
Hi, I have the following code:
SELECT dbo.t_admin.aUsername, dbo.t_admin.aPassword, dbo.t_adminSection.asSID
FROM dbo.t_admin INNER JOIN
dbo.t_adminSection ON dbo.t_admin.aID = dbo.t_adminSection.asAID
It brings back
james * pass * 1
james * pass * 2
james * pass * 3
However, I want it to bring back
james * pass * 1,2,3
How do I go about doing that?
Thanks
View 1 Replies
View Related
Feb 13, 2007
I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you
View 8 Replies
View Related
Jun 20, 2012
creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below
Itemcode, Description, Period1, Period2, Period3
001--------Desc--------233,---------,------- <<ROW1
001--------Desc ------------ ,100,------------ <<ROW2
001--------Desc ------,--------,-------,300 <<ROW3
Row one contains a value in Period1
Row two contains a value in Period 2
Row three contains a value in Period 3
I need to create a query so that the end result would look like this;
Itemcode, Description, Period1, Period2, Period3
001 --------Desc -------233 ----100 ---300
creating an sql query to get the above result?
View 2 Replies
View Related
May 20, 2008
i want to split the value of one column into multiple rows. based on comp capacity. pl help me in writing query
Actual table
Product IdProductQtyCompCapacity
1 8000 5000
2 10000 5000
4000
4000
Resultant table
Product IdProductQtyCompCapacity
2 50005000
2 50005000
1 40004000
140004000
View 6 Replies
View Related
May 31, 2014
I am using the following query to retrieve the top five customers from a MySQL table where 'accmanid' 1:
SELECT accmanid, custid, SUM(billone + billtwo) AS
total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 5
This outputs five rows ordered by the total bill where accman = 1.
Here is my question:
How can I write the query to output five rows for EACH accmanid; Im not sure how to do such without the WHERE clause.
View 2 Replies
View Related
Aug 10, 2005
ok my data base has 4 columns id,fname,lname,email and 3 rows 1,2,3
I made a simple update form that accesses the data and displays all three rows with the data in them:
<cfquery name="QUIZ" datasource="test">
SELECT id,fname,lname,email
FROM info
order by id
/cfquery
<HTML>
<HEAD>
<TITLE>Update an Employee</TITLE>
</HEAD>
<BODY>
<H1>Update an Employee</H1>
<FORM ACTION="databaseupdater.cfm" METHOD="POST">
<cfoutput query="QUIZ">
<INPUT TYPE="hidden" NAME="id" VALUE="#id#">
<P>
First name:
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(fname)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(lname)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="email" SIZE="15" MAXLENGTH="30" VALUE="#Trim(email)#">
<P>
</cfoutput>
<INPUT TYPE="submit" VALUE="update">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
you can use this form to send the updated data to databaseupdater.cfm:
<CFQUERY
DATASOURCE="test"
>
UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
</CFQUERY>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
Thank You
</body>
</html>
the problem Grieg RN is each column in the database gets updated with all 3 rows of data in each column in stead of seperating the data in its correct row and column. I don't know much about the problem im just starting out please help. Thanks
View 5 Replies
View Related
Jan 3, 2006
Hi all,
I need to write an SQL statement to update multiple row (all) as follws and can just not get it right please help.
I have two fileds - "Tilte" and "Title1" in my "notes" table.
I would like to run through the entire table and replace the information in "Title1" with that of "Title", also I need to change a character whilst doing so , ie change a "=" to a "-"
Example
Title - "Will is coming to town = Paul"
need to copy this to Title1 and it must change to...
Title1 - "Will is coming to town - Paul"
Running MS SQL2000 SP4
Help appriciated
Thanks
View 3 Replies
View Related
Apr 1, 2007
I have to update the domain name on our email database. Is there a simple update I can use to change firstname.secondname@olddomain.com to firstname.secondname@newdomain.com. I've got about 2800 records to update and I don't fancy either loosing them all by doing a dogy update query or doing it one at a time.
Any help would be greatly appreciated................
View 3 Replies
View Related
Jul 26, 2007
Hi all,
I'm trying to import around 4000 rows of data into SQL from an Excel spreadsheet. I thought I might be able to simply copy and paste into SQL Server (Tables > Right click on table > Open Table), but when I try this only one value is pasted in. There's only around eight columns and I've been trying to do each seperately but to no avail.
Can anyone suggest a means importing this data (as a whole, or in , either through some simple copy and paste function, or through some code?
TIA,
SamuelT
View 2 Replies
View Related
