Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
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 answer1
user1 answer2
user1 answer3
user2 answer1
user2 answer2
user2 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 answer3
user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,
Danny.
View Complete Forum Thread with Replies
Related Forum Messages:
PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns
Hello All, I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem. Consider that we have Product Category, Product and its monthly sales information retrieved as follows: CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600 I would like it to be converted into following result set: CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600 I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses. Thanks.
View Replies !
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
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 Replies !
Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows
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 Replies !
Merge Multiple Rows Into A One Or More Rows With Multiple Columns
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 Replies !
Converting Rows To Columns
have a urgent requirement. Please somebody help me.I have a table departinfo with following recordsbegin_time end_time Name Pieces10:00 10:15 PopCorn 310:15 10:30 Biscuits 510:30 10:45 PopCorn 2Now I need to run a sql query and the output should be as below :begin_time end_time PopCorn Biscuits10:00 10:15 3 010:15 10:30 0 510:30 10:45 2 0Please note that only one column i.e. PopCorn is created in spite ofhaving multiple records in the table. Similarly the records are notfixed. I mean thatthere can be n number of records and the columns should be uniquelycreated.Can somebody help me outPLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
View Replies !
Converting Rows Into Columns MS SQL 2K
I have a SP that returns the information I want but it returns it in 2separate queries.Example:Query 1Name, Number, ClassRow 1- Mike Phillips, 154AA, AAandQuery 2Time, ManualRow 1 -12:45:22,0Row 2 -13:04:56,0What I want it to look like is:Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0Here is the query I'm using:DECLARE Class cursorFOR--here we get a list of distinct classes to pass to the Class cursorselect Distinct(class_ID) from kt_member_lapwhere Race_ID = 83order by Class_ID;OPEN Class;DECLARE @RaceID intDECLARE@RacerCount intDECLARE @ClassID char(50)DECLARE @classcountDECLARE @Racer char(50)DECLARE @i intSET @RaceID = 83--this is where we loop through the classesFETCH NEXT FROM Class INTO @ClassIDWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)DECLARE Lap cursorFORSelect DISTINCT(Member_ID) from KT_MEMBER_LAPWhere class_ID = @classID and race_id = @RaceIDOPEN Lap;--this is to begin counting from the first lapSET @i = 1;FETCH NEXT FROM Lap INTO @RacerWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)SELECT KT_MEMBER.MEMBER_FNAME + ' ' +KT_MEMBER.MEMBER_LNAME As MemberName,CONVERT(nvarchar(3),KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER AsBikeNumber,KT_CLASS.CLASS_DESCFROM KT_CLASS INNER JOINKT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =KT_MEMBER_CLASS.CLASS_ID INNER JOINKT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =KT_MEMBER.MEMBER_IDWHERE KT_MEMBER.MEMBER_ID = @Racer and KT_CLASS.CLASS_ID =@ClassID--SELECT @Racer, @ClassIDSelect MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAPWhere Member_ID = @Racer and class_ID = @classID and race_id =@RaceIDORDER BY MEMBER_LAP_TIME_REAL--here I count up for the next lapSET @i = @i + 1;FETCH NEXT FROM Lap INTO @RacerENDCLOSE Lap;DEALLOCATE Lap;FETCH NEXT FROM Class INTO @ClassIDENDCLOSE Class;DEALLOCATE Class;Any help would be appreciated.
View Replies !
Converting Rows Into Columns
Hi How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?
View Replies !
Converting Rows Into Columns
Hi, I have written a query that I need to get data which are to be displayed in the SQL 2005 reports. I am getting the result as Data Count A 2 B 4 C 5 I need to get the data in a single row and the data in the 'Data' column should become like a column, like A B C 2 4 5 Can anyone help me with this query?
View Replies !
Converting Rows Into Columns
hi, i have the 4rows in one table those are book names... book1 book2 book3 book4 i have the other table..consisting of usenames in the output i need like this username1 book1 book2 book3 book4 username2 book1 book2 book3 book4
View Replies !
Converting Rows To Columns In SQL7
I do a:SELECT * FROM xxxAnd Get:Date Place SumA M 1A O 3 A P 2B O 5B M 4B P 2And I want it to look like:Date M O PA 1 3 2B 4 5 2Can you think of an EASY way to do this?I can do it with a cursor that constructs a SQL statement, which I EXEC, but the 8000 character limit may prove to be a limiting factor.sp_execsql is somewhat messy for the nature of this issue.Any input is appreciated.Thanks in advance.
View Replies !
Converting Rows To Columns Using Case.Problem With
SELECT h1.ftraccode, CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec', CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec', case when ftraaddred ='A' then h1.ftradesc end as 'From Description', case when ftraaddred ='r' then h1.ftradesc end as 'to Description' from bHISfile h1 where h1.ftradesc like 'sw%' order by 1 ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL 2(row) Expected output like this ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF 1(row)
View Replies !
Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View Replies !
Converting Repeating Fields In Single Row To Multiple Rows
Hi there I have loaded a csv file into a table. Some fields within the file contain a varying number (up to 10) of subfields seperated by line feed characters. It looks sort of like this Customer No. Payments Dates 111 pay1|pay2|pay3 dat1|dat2|dat3 I created an Unpivot transformation, and I got Customer No. Description Detail 111 Payments pay1|pay2|pay3 111 Dates dat1|dat2|dat3 After a Derived Column transformation I got Customer No. Description Line1 Line2 Line3 111 Payments pay1 pay2 pay3 111 Dates dat1 dat2 dat3 But what I really want is to end up with this: Customer No. Payments Dates 111 pay1 dat1 111 pay2 dat2 111 pay3 dat3 Is there a transformation that will get me there, or do I just need some cunning SQL? I tried to Pivot my way back to happiness but I couldn't get it to work
View Replies !
Multiple Rows And Columns MAX
Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.
View Replies !
Processing Multiple Rows And Multiple Columns In SSIS
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 Replies !
SQL Server - Multiple Rows Into Columns
Hello! I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures? Please let me know. Thanks much. -PV
View Replies !
Convert Multiple Rows Into Multiple Columns
I need to push multiple rows of data from one table to one record in another table. This can be achieved using cursors but I need to do them without using cursors. Source Table: fld_name fld_value fld1 val1 fld2 val2 fld3 val3 fld4 val4 Destination should be like this: fld1 fld2 fld3 fld4 val1 val2 val3 val4 Thanks.
View Replies !
Freezing Multiple Rows And Columns In Matrix Control
Hi all, I hv a matrix control in my rdl which looks like - Report Name (static row)Fund Name =Fields!FundName.Value(Dynamic Column Header) (static row)Broker Name =Fields!Broker.Value(Dynamic Column Header) (static row)Account No. =Fields!AccountNo.Value(Dynamic Column Header) ---then other static rows now i want these 3 column header's and there corresponding static rows should be freeze. Can anyone suggest me any solution for this. I can use either RS 2000 or RS 2005
View Replies !
Union Join To Return Multiple Rows Into Columns.
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt. 101 dues Mem 100 101 Vol charity 200 101 chapt CHi 300 I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed. 101 mem 100 charity 200 chi 300 Error: Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'PRODUCT_CODE'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. SELECTp.ID, p.PRODUCT_CODE as Chapt, p.product_code as Dues, p.product_code as Vol from ( SELECT ID, product_code as Chapt, Null as dues, Null as Vol from subscriptions where prod_type = 'chapt' AND BALANCE > 0 union all SELECT ID, Null as chapt, product_code as Dues, Null as vol from subscriptions where prod_type = 'dues' AND BALANCE > 0 union all SELECT ID, Null as chapt, Null as dues, product_code as Vol from subscriptions where prod_type = 'vol' AND BALANCE > 0 ) AS p GROUP BY p.id
View Replies !
Multiple Rows To One Row And Multiple Columns
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 Replies !
Data Flow: Converting Data In Multiple Columns
Hi, I'm just wondering what's the best approach in Data Flow to convert the following input file format: Date, Code1, Value1, Code2, Value2 1-Jan-2006, abc1, 20.00, xyz3, 35.00 2-Jan-2006, abc1, 30.00, xyz5, 6.30 into the following output format (to be loaded into a SQL DB): Date, Code, Value 1-Jan-2006, abc1, 20.00 1-Jan-2006, xyz3, 35.00 2-Jan-2006, abc1, 30.00 2-Jan-2006, xyz5, 6.30 I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View Replies !
Moving Rows Data Into Columns ..
Hi guys, I have a table with the following structure ID CodeID Code Desc 1 10 AA AAAAAAA 2 20 BB BBBBBBBB 3 30 CC CCCCCCCC 4 10 DD DDDDDDDD 5 20 EE EEEEEEEEEE Now I need to create a new table with the following structure CodeID AA BB CC DD EE 10 AAAAAAAA Null Null DDDDDD Null 20 Null BBBBBBBB Null Null EEEEEEEEE 30 Null Null CCCCCCCC Null Null I have managed to create a logic that solve this problem by selecting all AA values and insert it to the new table and then using a series of update statements I updated the values of BB, CC, DD €¦€¦ The problem is that I have to do a 30 update statement so the performance is very bad. Are there any better solution to implement this table using SSIS that could give me a better performance ? Thanks a lot.
View Replies !
Want To Convert Columns Data Into Rows...
Hello All, I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc.... now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id. Current Format :- code mail_address1 mail_address2 mail_address3 1111 abc@yahoo.com xyz@yahoo.com null Required format:- code sr_no mail_address 1111 1 abc@yahoo.com 1111 2 xyz@yahoo.com 1111 3 null i hope that i am clear with my question. Can any one help me? Thanks Prashant
View Replies !
Heap Table: Why 454 Rows Of Two INT Columns Use 2 Data Pages?
IF (SELECT OBJECT_ID('t1')) IS NOT NULLDROP TABLE t1GOCREATE TABLE t1 (c1 INT, c2 INT)DECLARE @n INTSET @n = 1WHILE @n <= 454BEGININSERT INTO t1 VALUES (@n, @n)SET @n = @n + 1ENDSELECT name, indid, CASE indidWHEN 0 THEN 'Table'WHEN 1 THEN 'Clustered Index'ELSE 'Nonclustered Index'END AS Type,dpages, rowcntFROM sysindexesWHERE id = OBJECT_ID('T1')name indid Type dpages rowcnt---- ----- ---- ------ ------NULL 0 Table 2 454I have a table containing 454 rows of two columnsof type INT with each being 4 bytesc1 int = 4 bytes+c2 int = 4 bytes=8 bytes per rowIf I entered 454 rows : 454 * 8 = 3,632 byteseach SQL Page is 8KB = 8 * 1024 bytes= 8,192 bytesa data page header takes the first 96 bytesleaving 8096 bytes for data and row offsets.Each record uses a row offset at the end of the pageconsisting of 2 bytes. 454 * 2 = 908 bytes.8096 - 3632 - 908 = 3,556 bytes. Should this befree data bytes?For a heap table, does SQL add an internal uniqueidentifiercolumn also? or my question is when does SQL adda uniqueidentifier? I am reading Inside SQL 2000 andtrying to understand a few things.A uniqueidentifier of 4 bytes gets added when a clustered indexexists but it is NOT a UNIQUE clustered index. AND onlyif duplicate record is added those two records only geta uniqueidentifier value.But in my example it's a heap table with no indexes. Evenon a heap table with no indexes a ROWID or Uniqueidentifierget added? Based on the INSERT statement above allvalues are unique.So what am I missing to understand why 453 rowsmake one data page to be used whereas 454 rowsmake two data pages to be used?Thank you
View Replies !
Turn Columns Into Rows And Rows Into Columns
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 There could be 1 item or 4000 items. What I want to see is. Item # - 123, 456 Description - Grenadine, Lime Juice CaseSalePrice - 100.00, 120.00 Cost - 75.00, 81.00 BottleSalePrice - 15.50, 17.25 Discount - 2.00, 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.
View Replies !
Insert Data Into Multiple Rows From ASP.net
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 Replies !
Multiple Rows Of Data Into 1 Row Results?
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 Replies !
Breaking A Row Of Data Into Multiple CSV Rows
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 Replies !
Displaying Data From Multiple Rows On 1 Row
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 Replies !
Updating Data In Multiple Rows..
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 Replies !
Select Data From Multiple Rows To One Row
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 Replies !
Selecting Data From Multiple Rows
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 Replies !
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment: Running this code on my PC via VS 2005 .Net version 2.0.50727 on the server (shown in IIS) Code is in ASP.NET 2.0 and is a VB.NET Console application SSIS 2005 Problem & Info: I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel. Desired Help: How to perform 1) stripping out all undesired rows 2) importing each column into sql table
View Replies !
Retrieving Multiple Rows From Data Base.
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 Replies !
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing: CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude)) INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'." Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values. The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why. Any ideas?
View Replies !
Help With Combining Data From Multiple Rows Into One Column In A View
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 Replies !
Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?
Hi, I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input. E.g. If I have the following data: Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald Second Input Key DontCareAboutThisField1 --- ----------------------- 1 ... 3 ... 4 ... Then I would like the following output: Main Output Key Name --- ---- 2 Jamie Second Output Key Name --- ---- 1 Steve 3 Donald Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated! Thanks in advance, Lawrie
View Replies !
Howto: Insert Multiple Rows Of Data From A Gridview Into A Sql Database
I have a gridview with rows of info which does not go into edit mode. A user clicks a checkbox which picks that item and adds a quantity which does some calculations. What i need to do is when a user clicks on a button, it should scan through the gridview for all items checked and pick up the quantity values and a couple of other field values and insert the information into a sql database What would be the best way of doing this? I tried the following example using a stored proc, but could not locate the proper reference or namespace for Database db = DatabaseFactory.CreateDatabase(); StringBuilder sb = new StringBuilder(); sb.Append("<absorbentitems>"); // Gets the selected rows in the gridview foreach (GridViewRow dvr in gvAbsorbents.Rows) { CheckBox checkbox = (CheckBox)dvr.FindControl("tbQty"); // Check to see if the CheckBox is checked or not if (checkbox.Checked) { // Make the string here sb.Append("<item code=""); sb.Append(((CheckBox)dvr.FindControl("lblItemCode")).Text); sb.Append("">"); sb.Append("</item>"); sb.Append("<itemordered qty=""); sb.Append(((CheckBox)dvr.FindControl("tbQty")).Text); sb.Append("">"); sb.Append("</itemordered>"); } } sb.Append("</absorbentitems>"); Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper insertCommandWrapper = db.GetStoredProcCommandWrapper("usp_InsertManyRows"); insertCommandWrapper.AddInParameter("@XMLDOC", DbType.String, sb.ToString()); try { db.ExecuteNonQuery(insertCommandWrapper); //Label1.Text = "Data Inserted"; } catch (Exception ex) { //Label1.Text = ex.Message; } Any way of making the above example work or any other better way of inserting multiple rows of data from a gridview into an sql database would be super. thanks!
View Replies !
|
TRACKER
