Pivoting row contents to produce one dimensional result I have a table with a varchar column containing pipe-delimited values

For example:

Row 1 Column 1 = a|b|e|gg|foo 
Row 2 Column 1 = oV|foo|do 
Row 3 Column 1 = boop


How can I query this to return something like? Duplicates are ok.

Results:

column
a
b
e
gg
foo
foo
oV
do
boop


I realize this is not optimal but my hands are tied with the current schema
If you cannot change your current schema, then you can create a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)&lt;1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)&gt;0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;


Then you can use an outer apply to join with your table:

select x.items
from yourtable t
outer apply dbo.split(col1, '|') x;


Which will produce the following results:

| ITEMS |
---------
|     a |
|     b |
|     e |
|    gg |
|   foo |
|    oV |
|   foo |
|    do |
|  boop |


See SQL Fiddle with Demo

Or if you do not want to use a split function, then you can perform this with CTE:

;with cte (value, fullValue) as
(
  select 
      cast(left(col1, charindex('|',col1+'|')-1) as varchar(50)) value,
      stuff(col1, 1, charindex('|',col1+'|'), '') fullValue
  from yourtable
  union all
  select 
    cast(left(fullValue, charindex('|',fullValue+'|')-1) as varchar(50)) ,
    stuff(fullValue, 1, charindex('|',fullValue+',|'), '') fullValue
  from cte
  where fullValue &gt; ''
) 
select value
from cte


See SQL Fiddle with Demo