Seperate lines into columns

Question

Post reply

Seperate lines into columns

Erik-316264

Right there with Babe

Points: 719
More actions
April 27, 2006 at 4:37 am

#112605

Hello,I have the following table:create table x ( id INT, t VARCHAR (200))insert x (id,t) values (1, 'These are three words')insert x (id,t) values (2, 'And this line contains more')goI would now like to create a new table that seperates the single words. I can show you what I want more clear in the result table that I would like to have:resulttable (id INT, t VARCHAR(200) )1, 'These'1, 'are'1, 'three'1, 'words'2, 'And'2, 'this'2, 'line'2, 'contains'2, 'more'Could someone show me how to get this table from the table x in a good way?Thank you again al lot!!!Erik

Viewing 6 posts - 1 through 5 (of 5 total)

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

Erik-316264 Right there with Babe Points: 719 More actions · Answer 1

Better readable:

Hello,

I have the following table:

create table x ( id INT, t VARCHAR (200))

insert x (id,t) values (1, 'These are three words')

insert x (id,t) values (2, 'And this line contains more')

go

I would now like to create a new table that seperates the single words. I can show you what I want more clear in the result table that I would like to have:

resulttable (id INT, t VARCHAR(200) )

1, 'These'

1, 'are'

1, 'three'

1, 'words'

2, 'And'

2, 'this'

2, 'line'

2, 'contains'

2, 'more'

Could someone show me how to get this table from the table x in a good way?Thank you again all lot!!!

Erik

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Here's one way...

--data

declare @x table ( id INT, t VARCHAR (200))

insert @x (id,t) values (1, 'These are three words')

insert @x (id,t) values (2, 'And this line contains more')

--calculation

DECLARE @Numbers_0_to_200 TABLE (i TINYINT identity(0,1), j BIT)

INSERT INTO @Numbers_0_to_200 SELECT TOP 201 NULL FROM master.dbo.syscolumns

declare @resulttable table (id INT, t VARCHAR(200) )

insert @resulttable

SELECT id, ltrim(rtrim(substring('¬'+t, i+1, charindex('¬',substring(t+'¬', i, 100))-1)))

FROM (select id, replace(t, ' ', '¬') as t from @x) a

INNER JOIN @Numbers_0_to_200 ON substring('¬'+t, i, 1) = '¬'

select * from @resulttable

I can guess what you're next question is going to be.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Erik-316264 Right there with Babe Points: 719 More actions · Answer 3

Dear Ryan,

Thanks for the answer, it works! However believe me I have more than one question. I haven't figured out yet, how this works.

Also the '¬' is new to me, give me some time to study this. I really have to learn all about T-SQL, And this set-based thinking is new to me.

But thanks anyway,

Erik

Erik-316264 Right there with Babe Points: 719 More actions · Answer 4

Hello,

OK I found the strange character. Stupid I did not see this at once.

What is the result of:

--calculation

DECLARE @Numbers_0_to_200 TABLE (i TINYINT identity(0,1), j BIT)

INSERT INTO @Numbers_0_to_200 SELECT TOP 201 NULL FROM master.dbo.syscolumns

And why are you using this contruction?

Thanks,

Erik

RyanRandall SSChampion Points: 13623 More actions · Answer 5

It's just a quick (and dirty) way to get a table of numbers. We need them from 0 to 200 because your string can be from 0 to 200 characters in length.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.