Sql Server Populate A Table Based On Another Table With A Substring As Column Name

I would like to populate a table based on a given table: Given table t1: id1 (string), id2 (string), value (float) tyb uanwe_A 6963 tyb uanw

Solution 1:

i hope this will help you

declare@temptable
 (id1 nvarchar(99), id2 nvarchar(99), valueint)
 insertinto@tempvalues ('tyb','uanwe_A',6963)       
 insertinto@tempvalues ('tyb','uanwe_B',979 )      
 insertinto@tempvalues ('tyb','uanwe_C',931 )   

select id1, substring(id2,1, 5) id2, 
        max(casesubstring(id2,7, 1)
        when'A'thenvalueend) vA,
        max(casesubstring(id2,7, 1)
        when'B'thenvalueend) vB,
        max(casesubstring(id2,7, 1)
        when'C'thenvalueend) vC
from@tempGROUPBY id1,substring(id2,1, 5)

Solution 2:

Try PIVOT

SQL Fiddle Demo

-- 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

I add another id1 to make the example more clear

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