CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Venkatesh R
Venkatesh R

Reputation: 514

Retrieve the value with Column name and corresponding Values in T-SQL

I need a select statement which will give column name in first result column and the corresponding value in next result column of the Query. the image refers the sample table. Test Table

when i retrieve the value for the ID 1 from test table. It has to give the result set like

Result set

I need to get an appropriate result even the column name is different no similar prefix or suffix in the column name. i do not want to change the Query to get the result even if Columns in the table are modified means added/removed.

Upvotes: 0

Views: 11679

Answers (3)

V.rr
V.rr

Reputation: 1

SQL Server 2016 (13.x) and later

declare @sql nvarchar(max);

set @sql = (select [YOUR_COL_NAMES] from [YOUR_TABLE_NAME] WHERE [CONDITION] for json auto , WITHOUT_ARRAY_WRAPPER);

select * from openjson(@sql)

Upvotes: 0

Simone
Simone

Reputation: 1924

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Val%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id,
        valname,
        valvalue
     from yourtable
     unpivot
     (
        valvalue
        for valname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

EDIT: in case you have just the first column fixed reverse the where clause:

...
          where C.table_name = 'yourtable' and
                 C.column_name != 'ID'
...

but all the credits have to be given to SSQL Server : Columns to Rows

Upvotes: 2

Shaun
Shaun

Reputation: 927

You could do it hitting the master table but probably a bit convoluted.

Easiest way would be just hard code the columns:

Select 'id1', val1 'STEP1', val2 'test2', val3 from [table]

Please don't put spaces in column names.

Upvotes: 0

Related Questions