sql not like




SQL服務器根據另一個表填充一個表,其中一個子字符串作為列名 (2)

我想根據給定的表填充表格:

給定表t1:

 id1 (string), id2 (string), value (float)
 tyb            uanwe_A        6963       
 tyb            uanwe_B        979       
 tyb            uanwe_C        931       

我需要 :

 id1, id2,   vA,   vB,    vC
 tyb  uanwe  6963   979  931 

我的SQL服務器查詢:

 select case substring(id2, 6, 1)
        when 'A' then [value]
        end as [vA]
 from t1

但是,這不適合我, 因為在子字符串(id2,6,1)不是'A'的情況下,我得到了許多“空”。

 select case substring(id2, 6, 1)
        when 'A' then [value]  end as [vA]
        when 'B' then [value]  end as [vB]
        when 'C' then [value]  end as [vC]
     end 
 from t1

我GOT錯誤:

  Incorrect syntax near the keyword 'when'.

任何幫助,將不勝感激。


我希望這能幫到您

 declare @temp table
 (id1 nvarchar(99), id2 nvarchar(99), value int)
 insert into @temp values ('tyb','uanwe_A',6963)       
 insert into @temp values ('tyb','uanwe_B',979 )      
 insert into @temp values ('tyb','uanwe_C',931 )   

select id1, substring(id2,1, 5) id2, 
        max(case substring(id2,7, 1)
        when 'A' then value  end) vA,
        max(case substring(id2,7, 1)
        when 'B' then value   end) vB,
        max(case substring(id2,7, 1)
        when 'C' then value  end) vC
from @temp GROUP BY id1,substring(id2,1, 5)

試試PIVOT

SQL小提琴演示

-- Pivot table with one row and five columns
SELECT [id1], [uanwe_A], [uanwe_B], [uanwe_C]
FROM
(   SELECT [id1], [id2], [value]
    FROM table1) AS SourceTable
PIVOT
(
AVG([value])
FOR [id2] IN ([uanwe_A], [uanwe_B], [uanwe_C])
) AS PivotTable;

OUTPUT

我再添加一個id1來使得這個例子更加清晰

| id1 | uanwe_A | uanwe_B | uanwe_C |
|-----|---------|---------|---------|
| tyb |    6963 |     979 |     931 |
| tyC |     111 |     222 |     333 |






sql-server-2008-r2