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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
View Related
How To Merge Multiple Rows Into Single Row
Hi, I have two tables of news feed NewsHeader & NewsDetails NewsHeader: Time Header 10:15:34 AM News1 10:15:34 AM News1 10:15:34 AM News1 11:19:39 AM News2 11:19:39 AM News2 12:35:04 PM News3 12:35:04 PM News3 NewsDetails Time Text RowC 10:15:34 AM ABC 1 10:15:34 AM DEFG 2 10:15:34 AM HIJKL 3 11:19:39 AM AABB 1 11:19:39 AM CCDD 2 12:35:04 PM ZZYY 1 12:35:04 PM XXWW 2 Required Output Time Header Text 10:15:34 AM News1 ABCDEFGHIJKL 11:19:39 AM News2 AABBCCDD 12:35:04 PM News3 ZZYYXXWW Thank you.
View Replies !
View Related
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 !
View Related
Making Single Column Value To Multiple Columns
I have a table which has single column like this. REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT I want to put this in a new table in individual columns Col1 col2 col3 col4 col5 col6 col7 col8 col9 REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT How to do this. Thanks.
View Replies !
View Related
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
View Related
Concatenate Column Value From Multiple Rows Into A Single Column
Hello, I need to concatenate a column from multiple rows into a single column in a new table. How can I do this? SID NAME PGROUP ------------------------------------------------------------ 3467602 CLOTHINGACTIVE 3467602 CLOTHINGDANCE 3467602 CLOTHINGLWR Need to have SID NAME PGROUP ------------------------------------------------------------ 34676 02 CLOTHING ACTIVE , DANCE, LWR THANK YOU
View Replies !
View Related
Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)
I hope i'm in the right place, but thanks anyway.... Actually i have 2 questions (regarding sql-server Indices/Keys): 1) I have an index, which is consisted of 4 columns. I've read elsewhere that this index functions (as well) as an index (single column index) on the first column of this multi-column index. Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns seperately i need to define only 3??? 2) I have a unique key consisted of multiple columns. I'd like to save an index to this combination of columns as well (to speed up things in DB...). Does the definition of a multiple-columns key free me from defining the multiple- columns index??? can anyone explain the main diference between Keys and Indices???
View Replies !
View Related
Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)
I hope i'm in the right place, but thanks anyway.... Actually i have 2 questions (regarding sql-server Indices/Keys): 1) I have an index, which is consisted of 4 columns. I've read elsewhere that this index functions (as well) as an index (single column index) on the first column of this multi-column index. Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns seperately i need to define only 3??? 2) I have a unique key consisted of multiple columns. I'd like to save an index to this combination of columns as well (to speed up things in DB...). Does the definition of a multiple-columns key free me from defining the multiple- columns index??? can anyone explain the main diference between Keys and Indices??? thanks, Ran Kizi
View Replies !
View Related
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 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 Replies !
View Related
Data Flow Task - Multiple Columns From Different Sources To A Single Table
Hi: I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks MA2005
View Replies !
View Related
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 !
View Related
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 !
View Related
Parse Delimited Data In Column To Multiple Columns
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character. So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep. Here is my table: CREATE TABLE invcdtl( invoicenum int, salesreplist [text] NULL, reprate1 int NULL, reprate2 int NULL, reprate3 int NULL, reprate4 int NULL, reprate5 int NULL, ) Here is some sample data: 1 A 0 0 0 0 0 2 0 0 0 0 0 3 I~~~~ 15 0 0 0 0 4 A~B 5 5 0 0 0 5 I~F~T~K~G 5 5 2 2 2 6 A~B As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~' Can anyone suggest a solution?
View Replies !
View Related
Need To Combine String Data From Multiple Columns Into One Column
When quering a table with given criteria, For ex: select notes, jobid, caller from contact where status in (6) and jobid = 173 I am getting this: This job will be posted to Monster for 2 weeks. 173 906 Waiting for full budget approval 173 906 TUrns out we're uppin 173 906 What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon? Please HELP!!!!!
View Replies !
View Related
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 !
View Related
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 !
View Related
Single Row Into Multiple Rows
Hi All, We've a table as in the following format: PK_Column1 PK_Column2 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Issue_Date2 Issue_Amount2 Issue_Category2 Issue_Reject2 We need to divide it into two new tables as follows: UniqueID PK_Column1 PK_Column2 And UniqueID PK_Column1 PK_Column2 Sequence_ID Issue_Date Issue_Amount Issue_Category Issue_Reject Unique1 1 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Unique2 2 Issue_Date2 Issue_Amount2 Issue_Category2 Unique3 1 xx xx Unique4 2 xx xx Unique5 3 xx 4 xx There will be one UniqueID for each row. We'll get the uniqueID and PK1 and PK2 in a file. Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table. Can we do this without using cursors? This is going to be one time process. Any ideas are appreciated. Thanks, Siva.
View Replies !
View Related
Getting Multiple Rows In A Single Row
Hi, I've a temp variable where I'm moving some columns like below: id value type1 type2 0 ab type1val1 type2val1 0 cd type1val1 type2val1 0 ef type1val1 type2val1 1 ab type1val2 type2val2 1 cd type1val2 type2val2 1 ef type1val2 type2val2 What I want to do is group these by their id and get the following o/p ab,cd,ef type1val1 type2val1 ab,cd,ef type1val2 type2val2 The grouped values need to be separated by commas. What I'm doing currently: I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p. Can anybody help me out? Thanks, Subha
View Replies !
View Related
Multiple Columns Into Single Row -- Very Urgent
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
View Replies !
View Related
Multiple Columns From Single Cell
Running SQL2000 I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist). This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL? Tables, Keys, and Relationships CREATE TABLE [dbo].[Flag] ( [FlagID] [int] NOT NULL , [FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StudentFlag] ( [StudentFlagID] [int] NOT NULL , [FlagID] [int] NULL , [StudentID] [int] NOT NULL , [FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED ( [FlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED ( [StudentFlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] ADD CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY ( [FlagID] ) REFERENCES [dbo].[Flag] ( [FlagID] ) GO Sample Output StudentID, FlagType1, FlagType2, FlagType3 1, 'yes', '2', null 2, null, null, 'X' 3, null, '7', 'X'
View Replies !
View Related
Spliting Single Row Into Multiple Rows
I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows. My table structure is: create table #scoresheet (Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255), Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255), Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255)); I would like to have three rows with four columns. What I've done so far is create a stored procedure that uses a table variable: create procedure sp_splitsinglerow as declare @Scoresheet_rows_table_var table ( ReviewArea varchar(25), NumericalValue int, VI varchar(10), Comments nvarchar(255)); insert into @Scoresheet_rows_table_var (ReviewArea, NumericalValue, VI, Comments) select Decisions, DNumericalValue, DVI, DComments from #scoresheet The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time. Any ideas on how to achieve that? BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)
View Replies !
View Related
Multiple Rows Into A Single Field
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this.. CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100) select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID SELECT TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1, dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id, dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone, dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age, dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type, TBL_TR_ACCIDENT_VEHICLE.Registration_Number, TBL_TR_ACCIDENT_VEHICLE.Crime_No, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre, TBL_TR_ACCIDENT_VEHICLE.vehicle_Make, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model, TBL_TR_ACCIDENT_VEHICLE.Unit_ID, TBL_TR_ACCIDENT_VEHICLE.RowID, TBL_TR_ACCIDENT_VEHICLE.UserID, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color, TBL_TR_ACCIDENT_VEHICLE.HP, TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity, TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle, TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight, TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight, TBL_TR_ACCIDENT_VEHICLE.Skid_Length, (select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state], (select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City] ---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/ (SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner end GO
View Replies !
View Related
How To Display Multiple Rows Of A Table In Single Row
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2
View Replies !
View Related
Inserting Multiple Rows With A Single INSERT INTO
Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)
View Replies !
View Related
Script To Combine Multiple Rows Into 1 Single Row
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View Replies !
View Related
Script To Combine Multiple Rows Into A Single Row
Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View Replies !
View Related
Return Multiple Rows Into One Single String
Hi, I need to return multiple rows into one single string Declare @String varchar(1000) Create table Cus (CusId Int,CusName varchar(10)) Insert into Cus Select 1,'John' Union All Select 2,'Bob' Select * from Cus returns 1John 2 Bob I need to return the all the rows from Cus table into a single string. The return is dynamic. I do not know the number of rows returned My result should be @String = 1,John,2,Bob How can i do that ?
View Replies !
View Related
Update Multiple Rows In A Single Query?
I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order. Code: update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111 PHP Code: $qt="update categories set "; for($i=0;$i<$num;$i++){ $a=$i+1; $qt.="sortorder=$i"; if($a<$num){ $qt.=", "; } } $qt.=" where userid=111"; Using PHP I can amend the loop above to slot in a row I want so I can change the sort order. unfortunately I'm not sure how to build such a query in mssql, can anyone help?
View Replies !
View Related
Combine Multiple Rows Into Single SQL Record
Hello: I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on. productID SectionID statusID 10 1 0 10 2 1 10 3 2 10 4 1 10 5 3 10 6 1 11 1 0 11 2 1 11 3 2 11 7 3 11 8 3 Need to return two rows with the respective values for each section. productID section1 section2 section3 section4 section5 section6 section7 section8 10 0 1 2 1 3 1 11 0 1 2 3 3 Any information or if you can point me in the right direction would be appreciated. Thanks
View Replies !
View Related
SQL 2000: Inserting Multiple Rows Into A Single Table
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
View Replies !
View Related
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 !
View Related
Copying Rows From Multiple Tables To A Single Table
Hi, I have 3 tables with the follwing schema Table <Category> { UniqueID, LastDate DateTime } Assume the follwing tables with data following the above schema Table Cat1 { 1, D1 2, D2 3, D3 } Table Cat2 { 2, D4 3,D5 4, D6 } Table Cat3 { 1, D7 3,D8 5,D9 } I have a Master and the schema is as follows Table master { UniqueId, Cat1 DateTime, -- This is same as the Table name Cat2 DateTime, -- This is same as the Table name Cat3 DateTime -- This is same as the Table name } After inserting the data from all these 3 tables, I want the my master table to look like this Table Master { UniqueId cat1 cat2 Cat3 ------------ --------- ------- ----------- 1 D1 NULL D7 2 D2 D4 NULL 3 D3 D5 D8 4 NULL D6 NULL 5 NULL NULL D9 } Please remember the column names will be same as that of table names can any one pelase let me know the query t o acheive this Thanks for your quick response ~Mohan Babu
View Replies !
View Related
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 !
View Related
To Alter Multiple Column With Single Statement
It is possible to alter multiple columns within a single alter table statement? I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement. http://www.blogcoward.com/archive/2005/05/09/234.aspx[^] Does anyone know about that? Thanks, Mushq
View Replies !
View Related
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 !
View Related
|
TRACKER
