Transact SQL :: Splitting Column Value While Keeping Existing Data
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example
PostCode Latitude Longitude
66000/66100 42.696595 2.899370
20251/20270 42.196471 9.404951
Would become
PostCode Latitude Longitude
66000 42.696595 2.899370
66100 42.696595 2.899370
20251 42.196471 9.404951
20270 42.196471 9.404951
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
This query was working well because I used to only be interested in one counter that was returned in the column, which was 'Free Megabytes'...I now have additional data that shows up as 'Total Disk Space'...Ideally, the query would return the total disk space next to the free megabytes on the same row for the same disk drive. Here is a couple rows of sample output:
AverageValueInstanceNameObjectNameCounterName 44549 C: LogicalDiskFree Megabytes 44548 C: LogicalDiskFree Megabytes 69452 C: LogicalDiskTotal Disk Space 69452 C: LogicalDiskTotal Disk Space
This is the ideal format, the average value column goes away:
InstanceNameObjectNameFree MegabytesTotal Disk SpaceC: LogicalDisk44549 69452
all the columns are separated with a "|" but the amount of columns are not fixed, so in lines 1 & 2 they are 4 columns and in line 3 there is 7 columns
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
I had to re-image my computer and reinstall SQL 2014. Now, when I open a table (edit top 200 rows) a tab opens (of course). But if I do not hit something like Show Criteria Pane and try to open another tab the first tab is replaced by the second table's tab.
How do I keep both open before hitting Show Criteria Pane?
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table 2. copy data into new column 3. drop indexes on column 4. <<<>>> now the existing column also has many SP dependent and I do not wish to drop them. 5. rename existing column to xxx 6. rename new column to correct column 7. drop old column 8. make required indexes
In the 70-461 objectives it says: Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications...I think I understand what this means in general. They want us to be able to create a view that will still be able to call the original data even if the table is modified. In other words, the view table shouldn't easily be broken. ie, type a code that does NOT ensure non regression, then change the code so that it does ensure non regression.
Hi Everyone,I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.sample data:create table tbl_list(pk_int_itmid int(5) Primary Key,vchar_desk vchar(300));create table tbl_test1(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itm varchar(60));insert into tbl_list values(1, 'this item');insert into tbl_list values(2, 'that item');insert into tbl_list values(3, 'those items');insert into tbl_test1 values(1, 'A, B - C, D, E - F, G, H - I');insert into tbl_test1 values(2, 'J, K - L, M, N - O');insert into tbl_test1 values(3, 'P, Q - R');into this table:create table tbl_output(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itmA varchar(60),vchar_itmB varchar(60),vchar_itmC varchar(60));Output in comma delimited form:'1', 'A', 'B', 'C''1', 'D', 'E', 'F''1', 'G', 'H', 'I''2', 'J', 'K', 'L''2', 'M', 'N', 'O''3', 'P', 'Q', 'R'my current solution:create view vw_itm_a as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom tbl_test1where charindex(',',vchar_itm) >1Gocreate view vw_itm_b as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom vw_itm_awhere charindex(',',vchar_itmC) >1;Gocreate view vw_itm_c as select fk_int_itmid, substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmCfrom vw_itm_bwhere charindex(',',vchar_itmC) >1;Go;create view vw_itm_d asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm_e asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm asselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_awhere fk_int_itmid not in (select fk_int_itmid from vw_itm_b)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_dunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_bwhere fk_int_itmid not in (select fk_int_itmid from vw_itm_c)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_eunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_cGo;select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC into tbl_outputfrom vw_itmIs there a much more efficient manner of handling this column splitting?ThanksDC
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
I have a table with millions of rows, 1 particular attribute "FromPerson" contains a string of email addresses, names, etc... for example (formatting done by this window... not exists in database...completely cleaned to semi-colon delimited)
tomh@gmail.com; Snyder, John N.; jsnyder@yahoo.com; Miller, Jim; millerj@gmail.com; Tenbrow, Jack; Katie Winslow (can be x number of names, emails...)
as you can see... some of the delimited values match up with an email address, others have just names, or emails... I have successfully split this into 1 row per value per say, but how do i get them to match up the values that do. I think the splitting is really not helping the cause... what i want is this...
tomg@hotmail.com tomg@hotmail.com Snyder, John N. jsnyder@yahoo.com Miller, Jim millerj@gmail.com Tenbrow, Jack Tenbrow, Jack Katie Winslow Katie Winslow
there could/can be other attributes, like datetime stamp, domain(if any)... etc... but I think I can add that later...
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I have to split a column using comma a delimiter into multiple columns. I am able to do it if i know how many column will be present in the final output. But in daily run, the columns may vary randomly.
how to split columns without hardcoding how many columns it ll come.
This is the code am using
Code: WITH Split_Names (Fil_id,Name, xmlname) AS ( SELECT Fil_ID,
I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?
Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)
I am trying to return as a dataset of this that shows the ID as column 1 then each value in the 2nd column as individual columns if I use SELECT LEFT('2ndColumn,PATINDEX(',',2ndColum)-1) I can return the first value as a column but then can't return any further values individually after the first column, I am just learning the new functionality in SSIS so not sure whether this would be my answer as apposed to T-SQL, if anyone has any advice on this it would be greatly appreciated?
Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie
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
how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values xxx|yyy|zzz|aaa|bbb|ccc|ddd into A B C D E F G xxx yyy zzz aaa bbb ccc ddd
This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.
Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address 101 E Commerce ST 120 E Commerce ST 2 Po Box 301 W. Bel Air Ave West Main Street, PO Box 1388
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
