CODEWITHSUNDEEP
sqlsql-server-2008
Sabari Ram
Sabari Ram

Reputation: 69

Displaying multiple columns from a single column in same row

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.

Upvotes: 2

Views: 3513

Answers (2)

Jade
Jade

Reputation: 2992

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'

Upvotes: 1

Dave C
Dave C

Reputation: 7392

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

Upvotes: 1

Related Questions