Loop through columns having name T-SQL

I have this query:

  Select ac.*,cp.NOMECAMPO as fieldname
FROM optes op 
    inner join  artico art on art.id=op.IDARTICO
    inner join SAM.ArtCla3ID13 ac on ac.idartico=art.id 
    inner join CAMPIPERS cp on cp.TABELLA = 'ArtCla3ID3'
WHERE op.id = 54782.000000 

that returns something like this:

    rivestimento | numtaglienti | raggio | diamscarico | fieldname    |
    ______________________________________
    nuda         |       0      |    0   |     1     |  diamscarico |
    nuda         |       0      |    0   |     1     |  diamscarico |

How can I have this?

diamscarico |  1
raggio      |  0
numtaglienti|  0
rivestimento|  nuda

thanks!

Answers


You can put the result of your query into an XML variable and do the unpivot when querying the XML.

declare @XML xml

set @XML = 
  (
    -- Your query goes from here
    select *
    from YourTable
    -- to here
    for xml path(''), type
  )

select T.X.value('local-name(.)', 'sysname') as ColumnNaame,
       T.X.value('./text()[1]', 'nvarchar(max)') as Value
from @XML.nodes('*') as T(X)

SQL Fiddle


Need Your Help

Salesforce 1 viewport issue in ios

html salesforce viewport visualforce apex

I have been creating visualforce pages for salesforce 1 mobile app for ios.

Tracking down which dependency contains SSE instructions

c++ visual-c++ sse

One of our customers needs a build of our program without SSE as he's on quite old hardware. My problem is that even if I modify our project settings across the board to unset SSE for all libraries...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.