CODEWITHSUNDEEP
sql-server
Dwayne Dibbley
Dwayne Dibbley

Reputation: 355

MSSQL join tables like pivot table in Excel possible?

I have two mssql tables with data like this:

table_fruit

id   valueid  valuetext
111  1        apple
111  2        pear
111  3        orange
112  1        kiwi
112  2        pear
112  3        banana

table_people

id  name
111 bob
112 george

I would like the end result on a single row like this using the above two tables:

name    fruit1  fruit2  fruit3
bob     apple   pear    orange
george  kiwi    pear    banana

I have tried joining the table linked on id, but end up with three rows per id in the results grid?

Upvotes: 0

Views: 49

Answers (4)

Esperento57
Esperento57

Reputation: 17492

Solution 3 :

select f0.name, 
f1.valuetext fruit1, f2.valuetext fruit2, f3.valuetext fruit3 
from table_people f0
left outer join table_fruit f1 on f0.id=f1.id and f1.valueid=1 
left outer join table_fruit f2 on f0.id=f2.id and f2.valueid=2 
left outer join table_fruit f3 on f0.id=f3.id and f3.valueid=3

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

Solution2 :

select f0.name,
Max(case when f1.valueid=1 then f1.valuetext else cast(null as varchar(1)) end) fruit1, 
Max(case when f1.valueid=2 then f1.valuetext else cast(null as varchar(1)) end) fruit2, 
Max(case when f1.valueid=3 then f1.valuetext else cast(null as varchar(1)) end) fruit3 
from table_people f0
left outer join table_fruit f1 on f0.id=f1.id
group by f0.id, f0.name

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

Solution 1 (not tested)

SELECT name, [0] fruit1, [1] fruit2, [2] fruit3
FROM
(
    SELECT f0.id, f0.name, f1.valuetext, f1.valueid  
    from table_people f0 left outer join table_fruit f1 on f0.id=f1.id
) AS SourceTable
PIVOT
(
MAX(valuetext) FOR valueid IN ([0], [1], [2])
) AS PivotTable;

Upvotes: 2

Thang Pham
Thang Pham

Reputation: 621

Yes, MSSQL supports PIVOT clause as well. You can refer to this article: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 0

Related Questions