Select Column where column <>0

Some preliminary clarifications:

• What you are looking for cannot be achieved with a classic SQL statement
• It is something that should be handled by the application layer and not by the engine.
• I think there shouldn't be more than one or two ways to solve it, and neither one is entirely optimal for the database engine (I insist on the previous point)

That said, let's move on to a solution using a dynamic query, the basic idea is:

• Do a UNPIVOTto transform the 11 columns into 11 rows
• Determine which columns have values ​​<> 0 and save them in a temporary table
• Create a dynamic statement that only considers those columns

First the UNPIVOT:

SELECT  Codigo, Campo, Sector, colname, colvalue
    INTO #MyTabla_Unpivot
    from (
            SELECT Codigo, Campo, Sector, SUM(Isnull([1],0)) AS [1], SUM(Isnull([2],0)) AS [2], SUM(Isnull([3],0)) AS [3], SUM(Isnull([4],0)) AS [4], SUM(Isnull([5],0)) AS [5], SUM(Isnull([6],0)) AS [6], SUM(Isnull([7],0)) AS [7], SUM(Isnull([8],0)) AS [8], SUM(Isnull([9],0)) AS [9], SUM(Isnull([10],0)) AS [10], SUM(Isnull([11],0)) AS [11]
            FROM MyTabla 
            WHERE V.Codigo=00173 and V.Campo=1 and V.Sector=2 
            GROUP BY Codigo, Campo, Sector, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
    ) 
    unpivot
    (
        colvalue
        for colname in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
    ) U
    WHERE colvalue <> 0

In #MyTabla_Unpivotwe will have an output similar to this:

codigo Campo       Sector      colname colvalue
------ ----------- ----------- ------- -----------
00173  1           2           8       27
00173  1           2           9       64
00173  1           2           10      15
00173  1           2           11      17

And now we create the dynamic statement:

DECLARE @sql NVARCHAR(MAX)

SELECT  @sql = 'SELECT Codigo, Campo, Sector'
SELECT  @sql = @sql + ',[' + CONVERT(VARCHAR, colname) + ']'
    FROM #MyTabla_Unpivot u GROUP BY colname

SELECT  @sql = @sql + char(13) + '     FROM #MyTabla'

PRINT   @sql -- Para revisar la sentencia
EXEC sp_executesql @sql -- Para ejecutarla efectivamente

Comments:

• It is worth clarifying that whenever you use dynamic statements you open the door to a lesser or greater degree to code injection, in this case it is a fairly controlled statement, but it is worth noting it
• This solution is consistent with more than one row

Here a fiddle with the idea of ​​this answer

You can do it with CASE and dynamically building the statement to execute, for example for columns 1,2 and 3 (for the rest it would be the same)

declare @sqlCmd as varchar(500)
set @sqlCmd = 'SELECT Codigo '
    + (SELECT CASE WHEN SUM(Isnull([1],0)) > 0 THEN ', SUM(Isnull([1],0)) AS [1]' ELSE '' END AS columnainventada FROM tabla Where Codigo = 'A' GROUP By Codigo)
    + (SELECT CASE WHEN SUM(Isnull([2],0)) > 0 THEN ', SUM(Isnull([2],0)) AS [2]' ELSE '' END AS columnainventada FROM tabla Where Codigo = 'A' GROUP By Codigo)
    + (SELECT CASE WHEN SUM(Isnull([3],0)) > 0 THEN ', SUM(Isnull([3],0)) AS [3]' ELSE '' END AS columnainventada FROM tabla Where Codigo = 'A' GROUP By Codigo)
    + ' FROM tabla WHERE Codigo = ''A'' GROUP BY Codigo'
-- select @sqlCmd
execute(@sqlCmd)

You can see this working here

I leave you a link (it is in English) where they explain a lot of casuistry of the CASE, in case what I put you does not adapt exactly to what you need.

Only complementing what was already there but without consulting the table at all times or using temporary tables.

This is just another alternative

    --Primero se declara una variable que contendra los campos a traer

    DECLARE @Fields NVARCHAR(MAX)
--creamos un CTE para traer los campos de forma dinamica
    ;WITH FormaCamposDinamicos AS(
    SELECT Codigo, Campo, Sector, 
    CASE WHEN SUM(ISNULL([1],0))=0 THEN '' ELSE '[1]' END +
    CASE WHEN SUM(Isnull([2],0))=0 THEN '' ELSE ',[2]' END +
    CASE WHEN  SUM(Isnull([3],0))=0 THEN ''ELSE ',[3]' END +
    CASE WHEN SUM(Isnull([4],0))=0 THEN '' ELSE ',[4]' END +
    CASE WHEN SUM(Isnull([5],0))=0 THEN '' ELSE ',[5]' END +
    CASE WHEN SUM(Isnull([6],0))=0 THEN '' ELSE ',[6]' END +
    CASE WHEN SUM(Isnull([7],0))=0 THEN '' ELSE ',[7]' END +
    CASE WHEN SUM(Isnull([8],0))=0 THEN '' ELSE ',[8]' END +
    CASE WHEN SUM(Isnull([9],0))=0 THEN '' ELSE ',[9]' END +
    CASE WHEN SUM(Isnull([10],0))=0 THEN ''ELSE ',[10]' END +
    CASE WHEN SUM(Isnull([11],0))=0 THEN ''ELSE ',[11]' END AS Data
    FROM MyTabla V
    WHERE V.Codigo=00173 and V.Campo=1 and V.Sector=2 
    GROUP BY Codigo, Campo, Sector, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
    --Al final seleccionamos el dato mas los campos
    SELECT @Fields='SELECT  Codigo, Campo, Sector'+Data+'FROM MyTabla' FROM FormaCamposDinamicos
    --al final lo ejecutamos
    EXEC (@Fields)

The code can be seen here http://sqlfiddle.com/#!18/6a796/3

Cheers