How To Create a Temp Table Out of Column Contents

Question

I have a table that has a contents field that is populated with IDs of items on that page. Like this:

ID                | Contents
Section-page-id   |  item-1 item-2 item-3 item-4 item-5
Section-page-id2  |  item-6 item-7 item-8 item-9 item-10

I would like to create a temp table that looks like the following:

Section-id       |ID
item-1           |Section-page-id 
item-2           |Section-page-id 
item-3           |Section-page-id 
item-4           |Section-page-id 
item-5           |Section-page-id 
item-6           |Section-page-id2 
item-7           |Section-page-id2 
item-8           |Section-page-id2 
item-9           |Section-page-id2 
item-10          |Section-page-id2

Is this even possible? Thanks in advance for your time.

This is a poor design. When you stuff multiple values into a single column like this it violates 1NF and is a serious pain to work with. You need a string splitter to parse this into something usable. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Sep 26 '16 at 16:22
There are lots of well documented answers on the subject of how to split a delimited string into rows. On this site and others. Recursive Common Table Expression Method, Table Valued Functions, XML. What have you searched and what have you tried? — Matt, Sep 26 '16 at 16:27

score 0 · Answer 1 · edited May 23 '17 at 12:00

From Turning a Comma Separated string into individual rows:

Declare @YourTable table
(
ID varchar(50),
Contents varchar(max)
)
Insert Into @YourTable values
('Section-page-id','item-1 item-2 item-3 item-4 item-5'),
('Section-page-id2','item-6 item-7 item-8 item-9 item-10')


;WITH CTE (ID, SectionId, Contents)  as (

select ID, LEFT(Contents, CHARINDEX(' ',Contents+' ')-1),
    STUFF(Contents, 1, CHARINDEX(' ',Contents+' '), '')
from @YourTable
union all
select ID, LEFT(Contents, CHARINDEX(' ',Contents+' ')-1),
    STUFF(Contents, 1, CHARINDEX(' ',Contents+' '), '')
from CTE
where Contents > ''
)
select SectionId, ID
from CTE
order by ID

Thanks a lot. This works perfectly and will be of major benefit. — Southerncentralrain, Sep 28 '16 at 14:19

How To Create a Temp Table Out of Column Contents

1 Answers1