Convert row to column using sql server 2008?

Table name is Looupvalue

id Ptypefield Value
1   1   D
2   1   E
3   1   F
4   1   G
5   1   H
6   2   FL
7   2   IF
8   2   VVS1
9   2   VVS2
10  2   VS1
11  2   VS2
12  3   0.50
13  3   1.00
14  3   1.50
15  3   2.00
16  4   Marquise
17  4   Round
18  4   Pear
19  4   Radiant
20  4   Princess

Lookupvalue table value convert roow to column depends on ptypefield

Like

id  1  id   2      id   3     id   4
1   D   6   fl      12 0.50    16   Marquise  
2   E   7   If      13  1      17    Round....   
3   F   8   vvs2    14  1.5
4   G   9   vvs2     开发者_如何转开发15 2
5   H   10  vs1
        11   vs2 

Thanks

In your sample output, it is not clear why values from columns 1 and 2 would be related to columns 3 and 4. However, here is a possible solution:

;With RowNumbers As
    (
    Select Id, PTypeField, Value
        , Row_Number() Over( Partition By PTypeField Order By Id ) As Rownum
    From #Test
    )
Select RowNum
    , Min( Case When PTypeField = 1 Then Id End ) As Id
    , Min( Case When PTypeField = 1 Then Value End ) As [1]
    , Min( Case When PTypeField = 2 Then Id End ) As Id
    , Min( Case When PTypeField = 2 Then Value End ) As [2]
    , Min( Case When PTypeField = 3 Then Id End ) As Id
    , Min( Case When PTypeField = 3 Then Value End ) As [3]
    , Min( Case When PTypeField = 4 Then Id End ) As Id
    , Min( Case When PTypeField = 4 Then Value End ) As [4]
From RowNumbers
Group By RowNum

If you wanted to dynamically generate the columns, the only way to do that in SQL is to use some fugly dynamic SQL. T-SQL was not designed for this sort of output and instead you should use a reporting tool or do the crosstabbing in a middle tier component or class.

This data schema looks like an EAV which would explain why retrieving the data you want is so difficult.