Displaying multiple columns from a single column in same row

Question

I have to show id, type and value (in three different columns) in single row for each id from the table having id, name and vale as columns as below.

Original table

ID  NAME    VALUE
1   Effort  10
1   Type    Development
2   Type    Testing
2   Effort  20
3   Type    Testing
3   Effort  20

Expected:

ID  TYPE           VALUE
1   Development 10
2   Testing         20
3   Testing         20

And following is the query i used to achieve the expected result:

select id as id, 
case name when 'Type' then value else null end as TYPE,
case name when 'Effort' then value else null end as value 
from tmn;

But I am getting a slightly different result form my expected one as:

ID  TYPE           VALUE
1              10
1   Development 
2   Testing 
2              20
3   Testing 
3              20

Mates, As i mentioned earlier, please help to achieve this.

Jade · Accepted Answer · 2014-02-25 04:55:10Z

1

Try this and let me know that satisfies you

SELECT  t1.ID,
        t1.Name,
        t2.Value
FROM    tmn As t1
        Left Outer Join tmn As t2
            On t1.ID = t2.ID
            And t2.Name = 'Effort'
WHERE   t1.Name = 'Type'

answered Feb 25, 2014 at 4:55

Jade

2,9941 gold badge13 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave C · Accepted Answer · 2014-02-25 04:50:01Z

1

Here is a code sample to obtain the desired results:

declare @test table (id int, name varchar(25), value varchar(25))

insert into @test (id, name, value)   
select 1,'Effort','10' union all
select 1,'Type','Development' union all
select 2,'Type','Testing' union all
select 2,'Effort','20' union all
select 3,'Type','Testing' union all
select 3,'Effort','20'

select t1.id, t2.value, t1.value
from (select id, value from @test where name='effort') t1
join (select id, value from @test where name='type') t2 on t1.id=t2.id

Edit: This code sample assume you have a effort/type entry for every id. If not, you may need to change to a full outer join, but will then possibly get null values back.

An alternate select statement would be:

select t1.id, t2.value, t1.value
from @test t1, @test t2 
where t1.name='effort'
and t2.name='type'
and t1.id=t2.id

edited Feb 25, 2014 at 4:50

answered Feb 25, 2014 at 4:44

Dave C

7,4821 gold badge21 silver badges30 bronze badges

1 Comment

Sabari Ram Over a year ago

also is it possible to get the unique type values and the sum as like below ID TYPE VALUE 1 Development 10 2 Testing 40

Collectives™ on Stack Overflow

Displaying multiple columns from a single column in same row

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related