showing column data as header

Ramkumar_S

Hi All, I have two table like this Table1 ------- empcode basic salary ------- -------------- 160 3000 170 4000 Table 2 --------- Amount Additions empcode ------ ---------- ------- 180 Bonus 160 25 Transport 160 so i want the output like this empcode basic Salary Bonus Transport ------- ------------ ----- ---------- 160 3000 180 25 179 4000 175 45 Please help me with this ..I am new to DB

Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com

PIEBALDconsult

Looks like you need a join.

Ramkumar_S

Thanks My Table 2 is dynamic value ..so header should change based on Addition column Table 2 --------- Amount Additions empcode ------ ---------- ------- 180 Bonus 160 25 Transport 160

Ramkumar ("When you build bridges you can keep crossing them. ") http://ramkumarishere.blogspot.com

Simon_Whale

Assuming you are using SQL Server. I would suggest that you read up on pivot queries. MSDN: Pivot Query[^]

Nagy Vilmos wrote:

And eat bacon. Bacon's real important for 'puters.

J4amieC

Assuming SQL Server, You can achieve this using the following PIVOT

select empcode, basicSalary, ISNULL(Bonus,0) as Bonus, ISNULL(Transport,0) as Transport
from
(SELECT s.empcode, s.basicSalary, a.description, a.amount
FROM salary s
LEFT JOIN additions a
ON s.empcode=a.empcode ) AS SalaryWithAdditions
PIVOT(
SUM(amount)
FOR description IN ([Bonus],[Transport])
) AS PivotTable

Output with your test data:

empcode basicSalary Bonus Transport
160 3000 180 25
170 4000 0 0

mhwasim

Check the following query... SELECT T1.empcode,T1.[basic Salary],B.Bonus,T.Transport FROM Table1 T1 INNER JOIN (SELECT Amount As Bonuus, empcode FROM Table2 WHERE Additions = 'Bonus') B ON B.empcode = T1.empcode INNER JOIN (SELECT Amount AS Transport, empcode FROM Table2 WHERE Additions = 'Transport') T ON T.empcode = T1.empcode Adjust the inner join to left join if required... Thanks