Essentially, how to transform the field name for the value?-CodePudding

Consult:

Data table is as follows:

The field name: a b c
-
Value: 1 2 3

Hope to get the data format is as follows:

The field name: header values
-- -- -- -- -- --
Value a 1
2 b
3 c
-- -- -- -- -- -- -- -- -- --

Don't want to use the method of the exec + string!

CodePudding user response:

 select * from (
Select 1 as id, 1 as a, as 2 b, 3 as c 
Union all 
Select 2,1,3,5 
) a 
Unpivot (
Value for the header in (a, b, c) 
) p

If there are no other columns are identified, then in turn column row_number before, otherwise unable to column turned

CodePudding user response:

If a \ b \ c these columns is not fixed, can only use the exec dynamic statement,

CodePudding user response:

Solution, the difficulty is how to obtain variable field names corresponding values,

Write a function that will be back to XML string table, field names can use SQL detected, type also had,

XML is left is to find the,