Girando o conteúdo da linha para produzir um resultado dimensional

Se você não puder alterar seu esquema atual, poderá criar uma função de divisão:

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)<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)>0)  
            insert into @temptable(Items) values(@slice)       

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

Então você pode usar um outer applypara se juntar à sua tabela:

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

Que produzirá os seguintes resultados:

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

Consulte SQL Fiddle com demonstração

Ou se você não quiser usar uma função de divisão, poderá fazer isso com 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 > ''
) 
select value
from cte

Consulte SQL Fiddle com demonstração

Supondo que seus dados contenham caracteres alfanuméricos simples e nada para confundir o XML, você pode usar esta consulta para executar alguma manipulação de texto para fazer com que seus dados pareçam XML e, em seguida, executar XQuery para dividir a coluna.

SELECT N.item.value('.','nvarchar(max)') item
FROM
(
     SELECT CAST('<a>'+REPLACE(Data,'|','</a><a>')+'</a>' as xml) X
       FROM Tbl T
) G
CROSS APPLY X.nodes('a') N(item)

Configuração do esquema do MS SQL Server 2012 :

CREATE TABLE Tbl (Data varchar(max));
INSERT Tbl VALUES
  ('a|b|e|gg|foo'),
  ('oV|foo|do'), 
  ('boop');

Resultados (SQL Fiddle) :

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

Uma maneira mais rápida de fazer isso do que usar XML ou uma função é usar uma tabela de contagem. O código a seguir é uma adaptação de um excelente artigo de Jeff Moden que eu recomendo fortemente.

create table #test
(
    Id int,
    string varchar(30)
);
insert into #test(Id, string) values
(1, 'a|b|e|gg|foo'),
(2, 'oV|foo|do'),
(3, 'boop');

declare @maxlen int;
select @maxlen = MAX(LEN(string)) from #test;
declare @delimiter char(1) = '|';

--A tally table generated from a CTE.  For small sizes (10K, at least)
--the CTE solution is markedly faster than a standard tally table.
WITH E1(N) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                                      --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b),    --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b),    --10E+4 or 10,000 rows max
cteTally(N) AS
(
    SELECT 0 UNION ALL
    SELECT TOP (@maxlen) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(Id, N1) AS
(
    SELECT
        test.Id,
        t.N + 1
    FROM
        cteTally t
        cross join #Test test
    WHERE
        SUBSTRING(test.String, t.N, 1) = @delimiter
        OR t.N = 0
)
--      Do the actual split. The ISNULL/NULLIF combo handles the length
--      for the final element when no delimiter is found.
 SELECT test.Id,
        ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(test.String, s.N1, ISNULL(NULLIF(CHARINDEX(@delimiter,test.String,s.N1),0)-s.N1,8000))
   FROM cteStart s
        cross join #Test test
  WHERE s.Id = test.Id;

drop table #test;

O código é um pouco mais complexo, mas o desempenho é significativamente melhor, especialmente porque o número de linhas que você precisa analisar aumenta.

Os primeiros CTEs (E1, E2 e E4) estão apenas configurando a tabela de contagem. O cteTally CTE então gera dinamicamente o comprimento ideal da tabela de contagem (o comprimento da string mais longa em sua tabela). Em seguida, cteStart encontra todos os pontos de separação. O resultado para o seu conjunto de dados se parece com:

+----+----+
| Id | N1 |
+----+----+
|  1 |  1 |
|  2 |  1 |
|  3 |  1 |
|  1 |  3 |
|  2 |  4 |
|  1 |  5 |
|  1 |  7 |
|  2 |  8 |
|  1 | 10 |
+----+----+

Interpretar isso é um pouco complicado porque o processo adiciona um pseudo-delimitador (se ainda não existir um) na primeira posição, e esta é uma string indexada. Assim, o N1 = 1 para as linhas 1, 2 e 3. 3 não possui mais delimitadores. 1 tem delimitadores extras nas posições 3, 5, 7 e 10, observando novamente que o caractere "a" está na verdade na posição 2 nesta configuração.

De qualquer forma, a consulta final faz a divisão real e retorna o conjunto de dados desejado.

A divisão da tabela de contagem é a melhor solução não CLR para o problema de divisão de strings. Se você tiver menos de 10.000 linhas, a tabela de contagem CTE é mais rápida, mas depois dessa marca de 10K, a solução da tabela de contagem padrão se torna melhor. Um módulo de divisão de string CLR seria mais rápido do que ambos, mas sei que muitas lojas hesitam em instalar módulos CLR.