CODEWITHSUNDEEP
sql-servert-sql
marco burrometo
marco burrometo

Reputation: 1135

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!

Upvotes: 0

Views: 1130

Answers (3)

marco burrometo
marco burrometo

Reputation: 1135

It works with static table names but i need it to be dynamic.

Here's my code

declare @artcla3 varchar(20)
declare @XML xml


select @artcla3='ArtCla3ID'+ convert(varchar,art.IDARTCLA3)
            FROM optes op inner join  artico art on art.id=op.IDARTICO
            WHERE op.id = 54782.000000 


exec sp_executesql N'
    set @XML = 
      (
        SELECT ac.*,cp.NOMECAMPO,cp.TITCAMPO
        FROM optes op 
            inner join  artico art on art.id=op.IDARTICO
            inner join sam.'+@artcla3+' ac on ac.idartico=art.id 
            inner join CAMPIPERS cp on cp.TABELLA = '''+@artcla3+'''
        WHERE op.id = 54782.000000 
        for xml path(''''), type
      )
', N'@XML xml',@XML=@XML


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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

EricZ
EricZ

Reputation: 6205

You can use UNPIVOT 

WITH tbl AS (
  Select ac.*
  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 
)
SELECT DISTINCT upp.column_name , upp.[value] 
FROM tbl
UNPIVOT
([value] for column_name IN ([rivestimento], [numtaglienti], [raggio], [diamscarico])) upp

Upvotes: 0

Related Questions