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

Question

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.

Edit your query and put the sample data and desired results in the query. External links are prone to failure. — Gordon Linoff, Nov 17 '15 at 13:38
No, columns in the table will be added/removed in the future. — Venkatesh R, Nov 17 '15 at 13:44
please check http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows — Simone, Nov 17 '15 at 13:45

score 2 · Accepted Answer · edited May 23 '17 at 11:52

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

Thank you for your Query Simone. But the Query is depends on the Column. Can't give assurance that all the columns will start with'Val'. Query should independent with Column name. — Venkatesh R, Nov 17 '15 at 13:48

score 0 · Answer 2 · answered Nov 17 '15 at 13:40

0

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.

answered Nov 17 '15 at 13:40

Shaun

933
9
16

I do not want change the select Query if any new columns are added in the table. Query should provide the result even the columns are added/removed on/from the table – Venkatesh R Nov 17 '15 at 13:43
Well use the master table. Then have a complex mess reliant on Microsoft and potentially fall apart on an upgrade. It's bad design to need it. – Shaun Nov 17 '15 at 13:50

score 0 · Answer 3 · edited Apr 27 '21 at 15:51

0

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)

edited Apr 27 '21 at 15:51

TT.

15,774
6
47
88

answered Apr 27 '21 at 15:18

V.rr

1
2

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

3 Answers3