single column data spit into multiple columns to another table

Question

single column data spit into multiple columns to another table

ashok_bayyana

SSC Journeyman

Points: 79
More actions
May 10, 2011 at 4:34 am

#235992

Hi
I have one table it is having only one colun length is 120 char. the data is like this
column name ID
d1012052011B10005280915A10004380920A10004360920
in the above data :-
d10 is code
12052011 is date
B100 is type
0528 is empcode
0915 is time
A100 is type
0438 is empcode
0920 is time
a100 is type
0436 is empcode
0920 time
like the above id fields is having length of 120 chara.
so, I want tha data is:-
empcode,date,type,time
so, pl help with sql query
Ashok

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the only way i think it's going to work is if you know each element is fixed width, then you can use a substring function to pull out each element.

I've exampled the first for elements for you to give you an idea how to do it:

/*--Results

code date type empcode

d10 12052011 B100 0528

*/

With mySampleData(ColumnData)

AS

(SELECT 'd1012052011B10005280915A10004380920A10004360920'

)

SELECT SUBSTRING(ColumnData,1,3) AS ,

SUBSTRING(ColumnData,4,8) AS [date],

SUBSTRING(ColumnData,12,4) AS [type],

SUBSTRING(ColumnData,16,4) AS [empcode]

/*0915 is time

A100 is type

0438 is empcode

0920 is time

a100 is type

0436 is empcode

0920 time

*/

From mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ashok_bayyana SSC Journeyman Points: 79 More actions · Answer 2

ashok_bayyana

SSC Journeyman

Points: 79

May 10, 2011 at 10:15 pm

#1322736

thanq

ashok_bayyana SSC Journeyman Points: 79 More actions · Answer 3

I want to save this temporary result set to some other table

Advance Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 4

you can simply use the SELECT... INTO TABLENAME FROM ... functionality to create the table, wethehr permenant or temp, of the fly:

SELECT SUBSTRING(ColumnData,1,3) AS ,

SUBSTRING(ColumnData,4,8) AS [date],

OtherColumns

INTO MyNewTableName --creates a new table filled with the columns from this select.

From MySampledata

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ashok_bayyana SSC Journeyman Points: 79 More actions · Answer 5

Hello sir

first let me say thanks

Your query is very good and working fine but in my table i have 13 rows like

column name is (ID)

d1012112010B20004180822A10006320845A100T5250847A10020280851A100F1480854A100T82859 d1012112010A10004920859A10020290900A10010640900A100T1980900A100F0510900A10006520900Ad1012112010A100T7650900C300T7650900A100F1500900A10013230901A100T5690901A10005900901Ad1012112010A10006450901A10018610902A10003430902A10004610902A10001270902A10002610902Ad1012112010A10005420902A10005280903A10006360903A10003930903A10006240903A10006470903Ad1012112010A10006500903A10015610903A10006430903A10005810903A10006110903A100T0070903Ad1012112010A10005610904A10006220904A10005970904A100T5310904A10020450904A10006030905Ad1012112010A100T8510910A100T3420910A100T2690910A100T9420911A10020400911 d2012112010C30006140811C30006480824C30012710824C30018190832A100T2420844C300F1480854Cd2012112010C300T1980900C300F0510900C30016730900C300F1500901C300T5690901A100F1320901Bd2012112010B200A0010902A10018610902B20002610902C30005280903C30003930903A10015220903C3d2012112010A100T0070904C30005610904C30005970904B200A0060904C300T5310904A100F1650911Ad2012112010A100F1650911

the above is synel data means emp. attendance data it shoud be converted to another table

like

type date code emp_code time

d10 12112010 B200 0418 0822

d10 12112010 A100 0632 0845

d10 12112010 A100 T525 0847

d10 12112010 A100 2028 0851

d10 12112010 A100 F148 0854

d10 12112010 A100 T825 0059

like that I have to create table of total 13 rows and above in this some rows will be 120 length

some will be less then 120 char with help of single column txt date I have to create table like above

columns

Pl. help me which will be thankfull

Ashok

Mike01 SSChampion Points: 11395 More actions · Answer 6

This is starting to sound like a homework project. What have you tried so far? Your original post said that it was a 1 column table that was 120 characters long. Now, your data seems to be larger than 120 characters. Looks like you might need to try substrings or a string parser, but I'd be interested in seeing what your attempts look like.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 7

my only suggestion is going to echo Mike; show us what you have so far; if the columns are variable length, I'd end up writing a program instead of trying to parse it via TSQL.

you probably know the data better, and what rules to apply, than what you've posted here; remember we are all remote volunteers not lookign over your shoulder; we can only infer info based on what you post.

all your examples start with "d10" or "d20" ; maybe that should be the starting point for your parsing of the data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

ashok_bayyana (5/11/2011)
Hello sir
first let me say thanks
Your query is very good and working fine but in my table i have 13 rows like
column name is (ID)
d1012112010B20004180822A10006320845A100T5250847A10020280851A100F1480854A100T82859 d1012112010A10004920859A10020290900A10010640900A100T1980900A100F0510900A10006520900Ad1012112010A100T7650900C300T7650900A100F1500900A10013230901A100T5690901A10005900901Ad1012112010A10006450901A10018610902A10003430902A10004610902A10001270902A10002610902Ad1012112010A10005420902A10005280903A10006360903A10003930903A10006240903A10006470903Ad1012112010A10006500903A10015610903A10006430903A10005810903A10006110903A100T0070903Ad1012112010A10005610904A10006220904A10005970904A100T5310904A10020450904A10006030905Ad1012112010A100T8510910A100T3420910A100T2690910A100T9420911A10020400911 d2012112010C30006140811C30006480824C30012710824C30018190832A100T2420844C300F1480854Cd2012112010C300T1980900C300F0510900C30016730900C300F1500901C300T5690901A100F1320901Bd2012112010B200A0010902A10018610902B20002610902C30005280903C30003930903A10015220903C3d2012112010A100T0070904C30005610904C30005970904B200A0060904C300T5310904A100F1650911Ad2012112010A100F1650911
the above is synel data means emp. attendance data it shoud be converted to another table
like
type date code emp_code time
d10 12112010 B200 0418 0822
d10 12112010 A100 0632 0845
d10 12112010 A100 T525 0847
d10 12112010 A100 2028 0851
d10 12112010 A100 F148 0854
d10 12112010 A100 T825 0059
like that I have to create table of total 13 rows and above in this some rows will be 120 length
some will be less then 120 char with help of single column txt date I have to create table like above
columns
Pl. help me which will be thankfull
Ashok

This is actually a cake walk if we can establish a couple of rules because there are no delimiters in the data...

In your original post, you said...

in the above data :-
d10 is code
12052011 is date
B100 is type
0528 is empcode
0915 is time
A100 is type
0438 is empcode
0920 is time
a100 is type
0436 is empcode
0920 time

The rules I want to know are as follows:

1. Will the "Code" column always be exactly 3 characters?

2. Will the "Date" column always be in the DDMMYYYY format"

3. Will the "Type", "EmpCode", and "Time" columns always contain exactly 4 characters each?

4. Will each record always start with the "Code" and "Date" columns?

5. Will #4 above always be followed by at least one occurance of the combination of "Type", "EmpCode", and "Time"?

6. Will "Type", "EmpCode", and "Time" always show up in that order?

7. Will the "Type", "EmpCode", or "Time" fields ever be missing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

I "normalized" the data you posted to find the "13 rows" you spoke of. As you can see, something is amiss...

d1012112010B20004180822A10006320845A100T5250847A10020280851A100F1480854A100T82859

d1012112010A10004920859A10020290900A10010640900A100T1980900A100F0510900A10006520900A

d1012112010A100T7650900C300T7650900A100F1500900A10013230901A100T5690901A10005900901A

d1012112010A10006450901A10018610902A10003430902A10004610902A10001270902A10002610902A

d1012112010A10005420902A10005280903A10006360903A10003930903A10006240903A10006470903A

d1012112010A10006500903A10015610903A10006430903A10005810903A10006110903A100T0070903A

d1012112010A10005610904A10006220904A10005970904A100T5310904A10020450904A10006030905A

d1012112010A100T8510910A100T3420910A100T2690910A100T9420911A10020400911

d2012112010C30006140811C30006480824C30012710824C30018190832A100T2420844C300F1480854C

d2012112010C300T1980900C300F0510900C30016730900C300F1500901C300T5690901A100F1320901B

d2012112010B200A0010902A10018610902B20002610902C30005280903C30003930903A10015220903C3

d2012112010A100T0070904C30005610904C30005970904B200A0060904C300T5310904A100F1650911A

d2012112010A100F1650911

One row is two characters short and another is 13 characters short instead of the expected 12 characters

which makes up a group of "Type", "EmpCode", and "Time".

Please explain your data above and post some "good" data in the form of an attached text file. And, if

you have 13 rows, then 13 rows should show up in the attachment instead of just 3 like you have in this

post. Otherwise, people just aren't going to be able to help you on this (what should be) easy problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Heh... took it one step further to see what was going on. Apparently, there's an extra field of information and a few more

rules that you eithe didn't know about or didn't tell us about. Help us help you... please explaing the inconsistancies in

the data you've provided and attach the data as a plain text file so we can try to help. Thanks.

[font="Courier New"]codddmmyyyy TypeEcodTime TypeEcodTime TypeEcodTime TypeEcodTime TypeEcodTime TypeEcodTime ???

d1012112010 B20004180822 A10006320845 A100T5250847 A10020280851 A100F1480854 A100T82859

d1012112010 A10004920859 A10020290900 A10010640900 A100T1980900 A100F0510900 A10006520900 A

d1012112010 A100T7650900 C300T7650900 A100F1500900 A10013230901 A100T5690901 A10005900901 A

d1012112010 A10006450901 A10018610902 A10003430902 A10004610902 A10001270902 A10002610902 A

d1012112010 A10005420902 A10005280903 A10006360903 A10003930903 A10006240903 A10006470903 A

d1012112010 A10006500903 A10015610903 A10006430903 A10005810903 A10006110903 A100T0070903 A

d1012112010 A10005610904 A10006220904 A10005970904 A100T5310904 A10020450904 A10006030905 A

d1012112010 A100T8510910 A100T3420910 A100T2690910 A100T9420911 A10020400911

d2012112010 C30006140811 C30006480824 C30012710824 C30018190832 A100T2420844 C300F1480854 C

d2012112010 C300T1980900 C300F0510900 C30016730900 C300F1500901 C300T5690901 A100F1320901 B

d2012112010 B200A0010902 A10018610902 B20002610902 C30005280903 C30003930903 A10015220903 C3

d2012112010 A100T0070904 C30005610904 C30005970904 B200A0060904 C300T5310904 A100F1650911 A

d2012112010 A100F1650911[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)