I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
1 ,AU-Australia 1,MM-Myanmar 1,NZ-New Zealand 1,PG-Paua New Guinea 1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country, Split.a.value('.', 'VARCHAR(500)') AS String FROM (SELECT id, country , CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE FROM @SPLITROW s INNER JOIN master.dbo.spt_values t ON t.type='P' AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:
Title - Author - Edition The Amazing Pixies - A. N. Author - Hdbk The Amazing Pixies - A. N. Author - Pbk
Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
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_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
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
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..
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
I have a matrix with single row. The no. of column varies and sometimes goes to 10-15. So it goes to next page and while exporting it inserts blank pages when exported to PDF. I need the column width at least 2.5cm. I need to break the matrix to next row instead of it going to next page say after 6th or 8th column. I tried to work with the example given in the site http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx by Chris Hays. But it is showing matrix for each Row Group, which doesn't meet my requirement.
I had a work around which worked by putting two matrix one below the other and filtering the columns to be shown in each matrix.
If anybody faced this issue or anybody solved the issue kindy reply which will be very helpful for me.
One more doubt, Can I get the Column number of the matrix?
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.
I have a string stored in field v_images in table tbl_DealerFeed_temp. This string is split by the "|" character. I want to create a SQL query to grab this string, split it, and insert it into tbl_Vehicles_photos. I also need to pass the value d_id into the photos table:
tbl_Vehicles_photos: vp_id, d_id, vp_image_name
The vp_id is the unique ID. I need to populate the d_id and vp_image_name field with my values.
I have a report and its been populating from a sproc. and i have 2 text boxes called both of them are poplulated by Fields!Investment Names, but right i can display the data left to right but i want to display the Data starting top to bottom and then towards the right.
I tried grouping the data in this way for one text box = CountRows()/2 > 10 . and this shows all the records one below the other, so is there a way that i can display half the records in one text box and the other half in the other text box.
I am going kinda nuts over this. Can someone please help me.
I imported all rows of my txt file using SSIS 2005 into a table. I am now trying to figure out how to split out the header, payment rows, and maintenance rows. First, some information.
An example of table results is here: http://www.webfound.net/split.txt The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row
The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).
I need to
1) Split out the header into a header table 2) Split out the maintenance rows (related to the header) into a maint table 3) Split out the payment rows (related to the header) into a payment table
I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.
To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31. If it contains 'MT' then you know it's a maintenance row, else it's a payment row.
