How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.
I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query: DECLARE @login char(20), /*This sets the rep for the query.*/ @record_date datetime, /*This is the date that we want to run this for.*/ @RWPY decimal(18,2), /*This is the required wins per year.*/ @OCPW decimal(18,2), /*This is the opportunities closed per week.*/ @OACW decimal(18,2), /*This is opportunities advanced to close per week.*/ @TOC decimal(18,2), /*This is the total number of opportunities in close.*/ @OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/ @TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/ @OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/ @TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/ @OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/ @TOI decimal(18,2), /*This is the total number of opportunities in interview.*/ @OCW decimal(18,2), /*This is the opportunities created per week.*/ @TOA decimal(18,2) /*This is the total number of opportunities in approach.*/ SET @login = 'GREP' SET @record_date = '12/18/2007' SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date))) SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date) SELECT loginname, CAST(@TOA AS decimal(18,1)) AS [Opps in Approach], app_time AS [Approach Average Time], app_perc_adv AS [Approach Perc Adv], CAST(@TOI AS decimal(18,1)) AS [Opps in Interview], int_time AS [Interview Average Time], int_perc_adv AS [Interview Perc Adv], CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate], dem_time AS [Demonstrate Average Time], dem_perc_adv AS [Demonstrate Perc Adv], CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate], neg_time AS [Negotiate Average Time], neg_perc_adv AS [Negotiate Perc Adv], CAST(@TOC AS decimal(18,1)) AS [Opps In Close], cls_time AS [Close Average Time], cls_perc_adv AS [Close Perc Adv] FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record. CREATE TABLE #pipelinehist ( glusftboid int IDENTITY(1,1) NOT NULL, record_date datetime NOT NULL, loginname char(20) NOT NULL, app_new float NOT NULL, app_time float NOT NULL, app_perc_adv float NOT NULL, int_time float NOT NULL, int_perc_adv float NOT NULL, dem_time float NOT NULL, dem_perc_adv float NOT NULL, neg_time float NOT NULL, neg_perc_adv float NOT NULL, cls_time float NOT NULL, cls_perc_adv float NOT NULL, target_deal money NOT NULL, annual_quota money NOT NULL, weeks int NOT NULL ) ON [PRIMARY] INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50) INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)
View Replies !
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 !
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 !
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 !
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 !
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client. The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View Replies !
Combine Multiple Records Into Single Row
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View Replies !
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 !
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
Select From Multiple Tables No Common Field
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question. SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
View Replies !
Multiple Tables Grouped By Common Field
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View Replies !
Combine Multiple Rows Into One
Hello, I have a delima, and im not really sure if this possible. But i have a table like lets say id | data1 1 this 2 that 3 stuff i want to be able to return this as one row with the data from data1 in one column seperated by commas. so the result would be 1 Column this, that, stuff can anyone help me with this. Thank you, ~ Moe
View Replies !
Combine Matching Multiple Rows Into One Row
IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq
View Replies !
Combine Data In Single Row
SELECT * FROM dbo.empBenefits q WHERE (StartDate IN (SELECT TOP 2 STARTDATE FROM EMPBENEFITS WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON' ORDER BY startdate ASC)) I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View Replies !
Grouping By One Field And Combine Data Of Another Field In SSRS
Hi all experters, Please suggest how to build the report in below case: Raw data: ID Member Functions 1 Alan A 1 Alan B 2 Tom A 2 Tom B 2 Tom C 3 Mary D 3 Mary E Report Shows: ID Member Functions 1 Alan A,B 2 Tom A,B,C 3 Mary D,E I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID. Any good suggestion? Thanks in advance, Steve Wang 2008/4/2
View Replies !
Multiple Foreign Keys On Same Field, Based On Other Field
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View Replies !
Updating Field Based On Record Count
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this. Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc All help is greatly appreciated.
View Replies !
Changing Alias Name Based On Record's Field Value
I have the following code, which works fine. I would like to change the alias name AMOUNT to something else, depending on the value of the transaction type. So if transaction_type = I then alias = AMOUNT_I; if transaction_type = R then alias = AMOUNT_R. Is this possible? Is there a better way to structure this query? I also tried using 2 sub selects - one for each transaction type - but there are about 8 other WHERE criteria on the T table (not shown) that had to be repeated, and it was just messy. Code: SELECT T.ACCT_CODE, T.TRANSACTION_TYPE, T.JUL_CURR_CREDITS + T.JUL_CURR_DEBITS + T.AUG_CURR_CREDITS + T.AUG_CURR_DEBITS + T.SEP_CURR_CREDITS + T.SEP_CURR_DEBITS + T.OCT_CURR_CREDITS + T.OCT_CURR_DEBITS + T.NOV_CURR_CREDITS + T.NOV_CURR_DEBITS + T.DEC_CURR_CREDITS + T.DEC_CURR_DEBITS + T.JAN_CURR_CREDITS + T.JAN_CURR_DEBITS + T.FEB_CURR_CREDITS + T.FEB_CURR_DEBITS + T.MAR_CURR_CREDITS + T.MAR_CURR_DEBITS + T.APR_CURR_CREDITS + T.APR_CURR_DEBITS + T.MAY_CURR_CREDITS + T.MAY_CURR_DEBITS + T.JUN_CURR_CREDITS + T.JUN_CURR_DEBITS AS AMOUNT FROM DBO.TOTALS T, DBO.ACCOUNTS A WHERE (T.TRANSACTION_TYPE='I' OR T.TRANSACTION_TYPE='R')
View Replies !
Selecting A Top Record Based On A Datestamp Field
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields: OrderID (FK, not unique) InstallationDate (Datetime) CreateDtTm (Datetime) There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier. For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly: SELECT a.OrderID, InstallationDate FROM ScheduleDateLog a INNER JOIN (SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord FROM ScheduleDateLog GROUP BY OrderID) as b ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord AND a.OrderID = b.OrderID
View Replies !
Show Multiple Order Record In Single Row
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients. I have data in both table like CompanyTableID Name1 name12 name2 OrderTable OrderId CompanyTabID 1 12 13 14 1 In my query I want to show all orders in single row. ID Name Orders1 name1 1,2,3,42 name2 null Is anybody can help on it. ThanksArvind
View Replies !
SP Return Multiple Records For A Single Record
I have two tables TermID, Term 1--- Abc 2--- Test 4--- Tunic and TermID, RelatedTermID 1 --- 2 1--- 4 2--- 4 I need to get back something like this TermID, Term, RelatedTermsInformation 1--- test--- test,tunic#1,4 that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray but I am not sure how this idea could be implemented using the capabilities of SQL. my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found. any ideas in how to make this better ?
View Replies !
Problem When Using Common Function To Modify Data Field
Hello, i created a chart that works correctly. But, i have field that display date and i wanted to change his format. So, in my chart.rdlc i choosed this field and with right click i modified his value by writing : =Format(Fields!DATEMODIFICATION.Value,"dd/MM/yyyy"). But an exception appear in my page and i can't found the problem. This is the exception : Failed to load expression host assembly. Details: Could not load file or assembly 'expression_host_d9a4a7394a924b25b6fb473b5463f83b, Version=10.7.50727.42, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418) Please, help me to found a solution Thanks
View Replies !
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 !
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 !
Automatically Update Another Field Based On Other Parts Of The Record???
Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record. CREATE PROCEDURE UpdateCartItem(@cartItemID Int,@newQuantity numeric(9))ASUPDATE CartItems Set quantity = @newQuantityWHERE cartItemID = @cartItemIDGO
View Replies !
Best Practice For Updating Single Field In N Rows
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance
View Replies !
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 !
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 !
Select Record Based On Multiple Criteria (vars)
Hi! I'm new to SQL and have a question... I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens. Basically my SELECT statement looks something like this, then is followed by an If... Else statement SELECT * FROM TableName WHERE LastName = varLastName AND FirstName = varFirstName AND Address = varAddress If RecordSet.EOF = True Then 'Item Not Found, add new record 'code to add new record...... Else 'Item Found, do nothing End If RecordSet.Update RecordSet.Close Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
View Replies !
How We Can Insert Multiple Query With Transaction Roll Bck For A Single Record
Hello, I have problem for insert multiple query for insert in differenr tabels for a single record. I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport, i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then........... so plz help me Thanks
View Replies !
DTS - Split Single Source Record (text) To Multiple Target (sql)
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database. As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file. For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure: [date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3] trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows: [date], [custid], [code1], [name1], [amount1] [date], [custid], [code2], [name2], [amount2] [date], [custid], [code3], [name3], [amount3] What is the best way to do this using DTS? I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work Vasilis Siatravanis, siatravanisv@interamerican.gr , vasilliss@hotmail.com
View Replies !
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 !
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 !
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 !
|
TRACKER
