I have an sql table called survey with rows: SurveyID, callref, question1, question2, question3, question4 etc. I want to get the information from the database with a select command like "SELECT question1, question2, question3, question4, question5, question6 WHERE SurveyID = 1" and write it to and array like this: aProfits As ArrayList = New ArrayList() aProfits.Add("question1value")aProfits.Add("question2value")aProfits.Add("question3value")aProfits.Add("question4value")aProfits.Add("question5value") Whats the best way of doing this? Andrew
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
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.
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.
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?
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
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 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!
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:
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
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.
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
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
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
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. :)
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