How to get this sql table from other table?

Is there a way to have something like:

id Name    value
--------------------
1  sex        m 
2  age        12
3  weight    200
4  height    200
5  rx         34

from a known table:

sex age weight height rx
--------------------------
m   12    200  200    34

If I do:

Select
    [id] = ORDINAL_POSITION,
    [Name] = COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'known'

I get:

   id Name    
    -----------
    1  sex     
    2  a开发者_JAVA技巧ge     
    3  weight  
    4  height  
    5  rx      

how to changethe query to get:

   id Name    value
    --------------------
    1  sex        m 
    2  age        12
    3  weight    200
    4  height    200
    5  rx         34

If they were 2 rows:

sex age weight height rx
--------------------------
m   12    200  200    34
f   34    245  111    67


id Name    value
--------------------
1  sex        m 
2  age        12
3  weight    200
4  height    200
5  rx         34
6  sex        f 
7  age        34
8  weight    240
9  height    111
10 rx         67

-----------------EDIT--------------------

Thanks for your answers, but I am wondering if this can be possible intead of getting

  id  value
    -------------------
    1  m
    2  12
    3  200
    4  200
    5  34

    from:

    sex age weight height rx
    --------------------------
    m   12    200  200    34

using

 Select
[id] = ORDINAL_POSITION,
[Value] ...
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'known'

The problem you're going to run into whatever method you choose is that all data in a particular column must be of the same type. In your case you have sex, which is a character, and a bunch of numbers (probably integers). This stops you being able to do this neatly as you cant use UNPIVOT. However there is always a way...

Given this setup code:

CREATE TABLE test(sex char(1), age int, weight int, height int, rx int)
INSERT INTO test
SELECT 'm', 12 , 200, 200, 34
union select 'f',34,245,111,67

You can do this, which is just a small addition to your query:

Select
[id] = ROW_NUMBER() OVER(ORDER BY isc.ORDINAL_POSITION),
[Name] = COLUMN_NAME,
[Value] = CASE LOWER(COLUMN_NAME)
            WHEN 'sex' THEN CAST(d.sex AS VARCHAR(20))
            WHEN 'age' then CAST(d.age AS VARCHAR(20))
            WHEN 'weight' THEN CAST(d.weight AS VARCHAR(20))
            WHEN 'height' THEN CAST(d.height AS VARCHAR(20))
            WHEN 'rx' THEN CAST(d.rx AS VARCHAR(20))
        END
from INFORMATION_SCHEMA.COLUMNS isc
CROSS JOIN dbo.test d
where TABLE_NAME = 'test'

Output:

1   sex m
2   sex f
3   age 12
4   age 34
5   weight  200
6   weight  245
7   height  200
8   height  111
9   rx  34
10  rx  67

You''ll notice this output is in a slightly different order to your own. This is because you have not described any key on your "known" table. If you did have a key on that table, you simply change this line:

[id] = ROW_NUMBER() OVER(ORDER BY isc.ORDINAL_POSITION),

to

[id] = ROW_NUMBER() OVER(ORDER BY d.yourKeyField, isc.ORDINAL_POSITION),

You can do it with PIVOT/UNPIVOT (see Books Online).

You can try this.

declare @T table
(
  sex char(1),
  age int,
  weight int,
  height int,
  rx int
)

insert into @T values
('m',   12,    200,  200,    34),
('f',   34,    245,  111,    67)

select row_number() over(order by (select 1)) as ID,
       T2.X.value('local-name(.)', 'varchar(128)') as Name,
       T2.X.value('.', 'varchar(10)') as Value
from (select *
      from @T 
      for xml path(''), type
     ) as T1(X)
  cross apply T1.X.nodes('/*') as T2(X)

This part order by (select 1) makes the assignment of ID's somewhat unpredictable. If you had a primary key (ID int identity) or a datetime to use in order by you could change that to order by ID instead.