I'm sure there is probably a very easy solution that I am just not seeing or can't Google...
I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?
I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.
For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.
Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?
I have some names stored in a table in the following format...
eg "Royales, Jon" "Smith, Alan" "Jones, Tom"
what I would like to see is...
"Jon Royales" "Alan Smith" "Tom Jones"
I assume I should search the string for a comma (,) and use split and then rejoin the values. I need to do this as part of my select statement... is it possible?
I have searched the forums for similar posts but didn't find anything helpful.
I found string from net how to split column data to row
SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM (SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c
When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , InvNoVARCHAR(10) NOT NULL , SalesCode NCHAR(80) NOT NULL
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.
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
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);
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX)) Insert INTO #Sample Values('1000') Insert INTO #Sample Values ('1024 AND 1025') Insert INTO #Sample Values ('109 ,110,111') Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544') Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added') Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX)) Insert INTO #Result Values('1000') Insert INTO #Result Values ('1024') Insert INTO #Result Values ('1025') Insert INTO #Result Values ('109') Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
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 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.
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.This is an example:
Code: SELECT item.uiGUID as ItemGUID, stores.strStoreName as Store, location.strLocationName as Location FROM tblItems as item INNER JOIN tblStoreLocations as location ON item.uiLocationGUID = location.uiGUID INNER JOIN tblStores as stores ON location.uiStoreGUID = stores.uiGUID WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))
When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.
When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.
I was to split each record into multiple columns. The problem is some records need to be split into only 1 column, others may need to be split into more. Also need to remove the "/"'s. This is all dependent on where a "/" is found. Been beating my head for a while and getting nowhere.
So:
create table #foo (myPK int, c1 nvarchar(425)) insert into #foo values (1,'/folder1') insert into #foo values (2,'/lvl1/folder2') insert into #foo values (3,'/folder1/lvl2/folder3') insert into #foo values (4,'/f1/folder2/lvl3/fldr4')
> SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans FROM mytab WHERE mrno = 'MR1514' and tranno = 1111 ORDER BY no_of_trans tranno medrecno createdon createdat nooftrans
I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31 ( M31_SQL_ID INT ,JOB_NUMBER INT ,LINE_NUMBER INT ,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242 16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245 16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
