how to change a data value to a column

Question

For example, I have a table like

Name    Property     Value

a            rec1           2

b           rec2             3

c            rec3            4

d            rec4            5

Now I need the result like this , should I create a  temp table ?

Rec1         Rec2           Rec3

2                 3               4

Answer 1

Select * from (select Property, Value from myTable) src PIVOT (max(Value) for Property IN ([rec1],[rec2],[rec3])) pvt

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

why use max ( ) ?  I need all the values

Answer 3

That is just the way a pivot works -- did you try what she gave you?  It ought to work.

Answer 4

Yes, I tried. It works, but only few columns, I guess that is because of max(). Do we have another way ?

Answer 5

Can you clarify again what you're trying to achieve and which values do you want to see or have missing?

MAX (or any other aggregate function) is required by the PIVOT command.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

I have property and value columns in a table. Now I want a new table displaying Property as column and value as row for each property.

Property   value   ------->              Property1   Property2   property 3

Property1  value1                           value1       value2        value3

property2  value 2

Property3 value 3

Answer 7

I suggest that you try the venerable MAX / CASE method.  I still think that you have not described your problem well enought, for example.

declare @test table
( property varchar(10), aValue varchar(10));
insert into @test
select 'Property1', 'value1' union all
select 'Property2', 'value 2' union all
select 'Property3', 'value 3'
;
select
  max(case when Property = 'Property1' then aValue else ''
  end) as Property1,
  max(case when Property = 'Property2' then aValue else ''
  end) as Property2,
  max(case when Property = 'Property3' then aValue else ''
  end) as Property3
from @test;
/* -------- Output: --------
Property1  Property2  Property3
---------- ---------- ----------
value1     value 2    value 3
*/

Now, since I still believe that this won't satisfy what you want, let me try to also ask what you might not have told us, looking at another angle:

declare @test table
( some_Id int, property varchar(10), aValue varchar(10));
insert into @test
select 1, 'Property1', 'value1' union all
select 1, 'Property2', 'value 2' union all
select 2, 'Property3', 'value 3'
;
select
  some_Id,
  max(case when Property = 'Property1' then aValue else ''
  end) as Property1,
  max(case when Property = 'Property2' then aValue else ''
  end) as Property2,
  max(case when Property = 'Property3' then aValue else ''
  end) as Property3
from @test
group by some_Id;
/* -------- Output: --------
some_Id     Property1  Property2  Property3
----------- ---------- ---------- ----------
1           value1     value 2    
2                                 value 3
*/

The disadvantage of the PIVOT operator is that it is fairly limited in the scope of circumstances that it can be applied.  There is a fair number of experts that previously have advised against using it for much other than simple problems.

One circumstance that will not work well is if you try to pivot more than one column.  Buyer beware; check your mileage to verify.

Answer 8

If you know the property values, then you can use PIVOT query as I showed in my post. If you don't know the property values, then you need to generate the PIVOT command dynamically, e.g.

declare @Cols nvarchar(max), @Sql nvarchar(max)

select @Cols = stuff((select ', ' + quotename(Property) 
from (select distinct Property from myTable) src Order by Property

FOR XML PATH('')),1,2,'')

set @SQL = 'SELECT * from (select Property, Value from myTable) src 
PIVOT (max(Value) for Property IN (' + @Cols + ')) pvt'

execute (@SQL)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

Use Pivot for this