Convert a single column into multiple rows

Question

Post reply

Convert a single column into multiple rows

vihaan

Old Hand

Points: 355
More actions
September 24, 2013 at 9:03 am

#298277

Hello
i have a big task in front of me
i have something like this in the text field. in a tableI have two columns Author ID and Description
the description field is as follows
Date: 09/04/2013 12:36:24 ReportName Person: Jack Jilll!!!!!!DD Name D/U R F PN!!!----------------------------------------------------------------------------------------------------------------------------------------!!!BookName1 100Pages Written Studymaterial!!!BookName2 200Pages Written Studymaterial!!!
The Above is the whole text . To get this i had to concatenate 2 fields and replace Char(13) with !!!.
All the other text is in the column value1 and columnvalue2.
Now from this i need to put Starting with BookName1 to end of !!! in one row and then BookName2 in second row. for that Author ID
So it should it be
AuthorID Description
1234 BookName1 100Pages Written Studymaterial
1234 BookName2 200Pages Written Studymaterial
I used !!! to seperate which text gets into next row.
I dont need anything that is before the BookName1
Can you please suggest a way to do this.

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 1

Hi...

1. The first question I have is whether !!! can appear anywhere else in the text. Probably not, but one should ask.

2. You have a row delimiter of !!! but what is the column delimiter? If there is none, is there a fixed length?

3. You will probably have to use RBAR -- a cursor -- and split the description into varchar fields with a UDF to do so, and then insert. Finally delete the row with the garbled data.

Thanks

John

vihaan Old Hand Points: 355 More actions · Answer 2

Hello ,

why do we need a column delimiter as its going to be only one column but different rows.

can u give a example in this case

Thanks

madhavi

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 3

RBAR shouldn't be necessary here; give this article a read:

DelimitedSplit8K[/url]

Once you create the DelimitedSplit8K function, you can toss it at your table, like so:

SELECT * FROM YourTable

CROSS APPLY YourDB.YourSchema.DelimitedSplit8K(YourDB.YourColumn,'!!!')

WHERE Item <> ''

Replace the "Your" parts with your actual data structures, of course, and trim that SELECT * down to the data you actually need. Doing so will split everything out just the way you requested it in your post; you'll need to do some further filtering to clear out the other unnecessary parts, like the date and so forth, and I'm not sure how you assign the authorIDs in the table. With what you gave, though, this should produce a speedy solution.

- 😀