Selecting Data From Multiple Rows
Jan 22, 2008
Here's my tables:
Students
-----------------------
StudentID | StudentName
-----------------------
StudentClasses
-----------------------
StudentID | ClassName
-----------------------
I'm trying to put data into a gridview in ASP.NET 2.0 like this:
Student | Classes
-----------------
Name1 | ClassName1 ClassName2 ClassName3
Name2 | ClassName1
Name3 | ClassName1 ClassName2
I just need to retrieve names of each class that a student is taking, then print them out one after another.
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hello All,I have this table:CREATE TABLE [dbo].[tbl_TESTING] ([ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,[FNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[MOVEDINYR] [int] NULL ,[MOVEDOUTYR] [int] NULL) ON [PRIMARY]GOwith these records:INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1995', '2000')INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1994', '2005')What I would like to do is be able to select FIRSTNAME, LASTNAME,MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.FIRSTNAME=JAMESLASTNAME=TAYLORMOVEDINYR=1994MOVEDOUTYR=2005Some sql syntax help appreciated,thanks in advance!
View 2 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Aug 4, 2006
Hi,
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Many thanks
Rob Gibson
View 5 Replies
View Related
Aug 29, 2014
I need to extract a price from a package solution table that was current on a certain date.
Columns are:
Product_Reference int,
Change_Date int, -- Note that this is in yyyymmdd format
Price decimal
So for one items you would get
Product_ReferenceChange_DatePrice
100014200401281.59
100014200605131.75
100014200802121.99
100014200906252.35
100014201002242.50
100014201107151.10
100014201205151.15
The challenge is to return the price of the item on 20070906. In this example 1.75.
I've tried joining it to itself
SELECT DISTINCT p1.[Product_Reference]
,p1.[Change_Date]
,p1.[Price]
FROM PHistory p1
INNER JOIN PHistory p2
ON p1.Product_Reference = p2.Product_Reference
WHERE p1.Product_Reference = 100014
AND p1.Change_Date >= 20070906
and p2.Change_Date < 20070906
But it returns the price above and below that date.
View 3 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 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
Sep 22, 2005
oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
View 2 Replies
View Related
Jun 17, 2015
writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?
View 1 Replies
View Related
Oct 9, 2015
I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster
The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)
The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.
I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.
The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?
View 5 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
Dec 10, 2003
Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.
I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).
Thanks!
View 5 Replies
View Related
Nov 3, 2005
I am stuck. I have some vars being passed to an aspx page that I need to dump into a db table in multiple rows, how do I do it? I am going into a SQL database using VS.Net 2003 here's the format that the vars come in the page as: UserID = 46 k12SessionArray0interaction_id = Interaction_01 k12SessionArray0correct_response = d k12SessionArray0student_response = d k12SessionArray0result = C k12SessionArray0latency = 00:00:02 k12SessionArray1interaction_id = Interaction_02 k12SessionArray1correct_response = c k12SessionArray1student_response = c k12SessionArray1result = C k12SessionArray1latency = 00:00:02 k12SessionArray2interaction_id = Interaction_03 k12SessionArray2correct_response = a k12SessionArray2student_response = a k12SessionArray2result = C k12SessionArray2latency = 00:00:03 now here's the format of the database AnswerID | UserID | InteractionID | CorrectResponse | StudentResponse | QResult | Latency How do I insert the data to have it be mulitiple rows like: 1 | 46 | Interaction_01 | d | d | C | 00:00:02 2 | 46 | Interaction_02 | c | c | C | 00:00:02 3 | 46 | Interaction_03 | a | a | C | 00:00:03
View 4 Replies
View Related
Aug 22, 2005
I would like to display data from one column on multiple rows in one row.
Example:
Name Pet
David Dog
Dawn Dog
Dawn Cat
Pete Mouse
Pete Cat
Pete Dog
I would like the result to do the following:
David Dog
Dawn Dog Cat
Pete Dog Cat Mouse
I will not know the row values ahead of time.
View 2 Replies
View Related
Jun 19, 2007
Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.
Basically I need to do the following.
Selected from database:
Row1 Col1 Col2 Col3
Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi
Thank you
View 5 Replies
View Related
Feb 5, 2015
I am running a query to pull data from 2 tables. However multiple data elements could be attached to one unique ID which when I run the query it repeats causing the entire data set to give inaccurate numbers. How to achieve this:
xxx.001A3264
xxx.001A3685
xxx.002A3261
xxx.002A3685
I would like my results to look like this:
xxx.001A3264 & A3685
xxx.002A3261 & A3685
View 2 Replies
View Related
Feb 27, 2008
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.
stocknumber transtype transsubtype balance
s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100
s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300
I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below
stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400
I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
View 7 Replies
View Related
Dec 3, 2005
I have a table that has values as follows:PersonID Degree55 MD55 Phd55 RN60 MD60 PhdI need a create a query that will give me output like this:PersonID Degree55 MD, Phd, RN60 MD, PhdAny ideas
View 16 Replies
View Related
Sep 20, 2007
Hi all..
First of all Thanks for all the help, I received over the years from MSDN..
Here is my new problem...
I have a SQL table like following table..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
12/31/2049
Now here is what I want to do€¦
1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage
2> select the rows with same city (rows 1-6)
3> Select the rows with distinct start date (rows 1-3-5)
4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 €“ 1 day = 3/31/2007)
5> proceed till end of selected rows.. 1-3-5
6> do the same thing for rows 2 and 4.
7> follow the same procedure for rest of the file.
The selected file should look like this when done..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
9/30/2006
Is there way to do this in SSIS? any recommened appprach>?
Any help with this is highly appreciated..
Thank You..
View 1 Replies
View Related
Sep 15, 2006
OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim searchString As String
searchString = Me.SearchStringBox.Text
Try
Dim filter As String
filter = "LastName LIKE '" & searchString & "'"
Dim search() As System.Data.DataRow
search = myCDDataSEt.ClientData.Select(filter)
If search.Length > 0 Then
'no code as yet
Else
MessageBox.Show("The client " & searchString & "is not in the database")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
View 2 Replies
View Related
Aug 13, 2006
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
View 2 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Apr 1, 2015
How to split a column data into multiple rows, below is the requirement...
Create table #t3 (id int, country (varchar(max))
INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'
Output should be like below
1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
View 4 Replies
View Related
Jul 19, 2007
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View 1 Replies
View Related
Apr 29, 2008
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
View 3 Replies
View Related
Nov 4, 2015
Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.
View 9 Replies
View Related
Jan 9, 2008
Hi,
I want to load data into Excel file with following format,
Country
State
Total
Location
ABC
A
20
X1
30
Y1
C
100
XYZ
X
40
Basically I want to insert records from multiple rows into a single row; how can I achieve this using SSIS.
I am using Excel as a data source.
Any help is appreciated.
Regards,
Omkar.
View 8 Replies
View Related
Nov 28, 2006
Hi
in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").
We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.
How can we achieve that with a vb.net data script task ? (Or anything else ?)
best regards
Thibaut
View 9 Replies
View Related
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
Jan 7, 2002
how can I select 10 random rows from a table?
Please help
View 2 Replies
View Related
Sep 12, 2001
Hello,
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length.
Any ideas people?
Thanks,
Alan
View 2 Replies
View Related
Nov 28, 2007
Hi,
I've a table the following table:
ActDate datetime
CustomerID int
data on the table:
6/27/2007 10:23:22 AM
113
6/27/2007 10:25:20 AM
113
6/27/2007 11:38:34 AM
76
6/27/2007 5:23:22 PM
212
6/28/2007 11:58:58 AM
110
6/28/2007 12:24:17 PM
110
6/28/2007 12:27:15 PM
110
7/3/2007 5:16:44 PM
63
7/3/2007 5:16:56 PM
63
7/12/2007 3:35:50 PM
98
7/13/2007 3:30:05 PM
110
7/13/2007 3:32:49 PM
110
7/13/2007 3:38:01 PM
110
7/13/2007 3:46:00 PM
110
7/14/2007 10:25:55 AM
82
7/15/2007 9:19:11 AM
18
7/21/2007 3:53:16 PM
34
7/23/2007 11:53:43 AM
84
7/23/2007 11:55:24 AM
84
7/23/2007 11:55:48 AM
84
7/24/2007 7:41:31 PM
116
7/27/2007 9:39:52 AM
110
7/27/2007 12:41:49 PM
18
7/28/2007 11:00:55 AM
113
7/31/2007 10:11:31 AM
82
8/1/2007 5:56:23 PM
98
8/2/2007 7:22:16 PM
77
8/2/2007 8:28:11 PM
77
I need to filter the table in order to have only a record by CustomerID with the higher date.
Thanks in Advance
JP
View 3 Replies
View Related
