CODEWITHSUNDEEP
sqlsql-servert-sqlpivot-tableinformation-schema
user6408649
user6408649

Reputation: 1305

Column names in result and rotate part of table

The following a table structure:

'----ID-----'----NAME----'----FIELD1----'----FIELD2----'
'     1     '    val     '     123      '      321    '
'     2     '    val2    '     234      '      212    '

Need to get the following result:

'----ID-----'----NAME----'----FIELDS----'----VALUES----'
'     1     '    val     '    FIELD1    '      123     '
'     1     '    val     '    FIELD2    '      321     '
'     2     '    val2    '    FIELD1    '      234     '
'     2     '    val2    '    FIELD2    '      212     '

How write this query? I can get column names from INFORMATION_SCHEMA.COLUMNS. But how to join table with INFORMATION_SCHEMA.COLUMNS? Also how can rotating a part of table?

As living example. Following is table:

enter image description here

On screenshot only several fields but in table there are a lot of fields. I wrote the following query:

Select p.GUID, p.myvalues, p.Fields 
from myTable gz
unpivot( [myvalues] for Fields in ([area], [davlplastmax])) p

But this query doesn't return null values.

Also I want get columns from INFORMATION_SCHEMA.COLUMNS and past them in ([area], [davlplastmax]).

For example:

unpivot( [values] for Fields in (
    SELECT [MyDb].INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
    FROM [MyDb].INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = N'MyTable'
)

Upvotes: 1

Views: 110

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use unpivot as below:

Select * from #data
unpivot( [values] for Fields in ([Field1],[Field2])) p

Output as below:

+----+------+--------+--------+
| Id | Name | values | Fields |
+----+------+--------+--------+
|  1 | val  |    123 | Field1 |
|  1 | val  |    321 | Field2 |
|  2 | val2 |    234 | Field1 |
|  2 | val2 |    212 | Field2 |
+----+------+--------+--------+

You can use dynamic query as below for getting columns from Information_Schemas

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((select ','+QuoteName(Column_Name) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestData'
        and COLUMN_NAME not in ('Id','Name') for xml path('')),1,1,'')

Select @query = '   Select * from
    (Select * from #data )a
    unpivot( [values] for Fields in (' + @cols1+ ')) p '

Exec sp_executeSql @query

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Unpivot?

select u.id, u.name, u.fields, u.values
from MyTable t
unpivot
(
  values
  for fields in (Field1, Field2)
) u;

Upvotes: 2

Related Questions