How to Write A Query?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Write A Query??

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2009-05-06 : 05:23:28

Hi all,

I have 3 tables:

EquipmentTable:
Equipment_ID
Equipment_Description

EquipmentOwnedTable
Individual_ID
Equipment_ID
Amount

IndividualTable:
Individual_ID
Individual_Name

Now I would like to make a query where the result should return like the following:

Result
Individual_ID
Equipment A
Equipment B
Equipment C

Where under the Equipment A its the Amount of Equipment A of the individual from EquipmentOwnedTable

Can anyone help me out?

Thanks!
OB

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-06 : 05:34:13

Hi, Can u Send the Sample data

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-06 : 06:00:38

[code]
SELECT o.Individual_ID, Amount = SUM(o.Amount)
FROM EquipmentOwned o
GROUP BY o.Individual_ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

oceanboy
Starting Member

44 Posts

Posted - 2009-05-06 : 06:37:10

Equipment Table
===============
Eq_ID Description
----- ---------------
1 TV
2 Radio
3 Laptop
.....

EquipmentOwned Table
=====================
Ind_ID Eq_ID Amount
------ ----- ------
1 1 5
1 2 4
1 3 5
2 1 3
3 2 4
3 3 1

Individual Table
================
Ind_ID Name
------ ----------
1 Richard
2 David
3 Sandra
.....
.....

The result I hope to get is
Ind_ID TV Laptop
------ ---- -------
1 5 5
2 3 -
3 - 1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-06 : 07:09:55

[code]select
a.Ind_ID,
b.[Description],
sum(a.Amount) when
from
EquipmentOwned a left join Equipment on a.Eq_ID=b.Eq_ID
group by
a.Ind_ID,
b.[Description][/code]

oceanboy
Starting Member

44 Posts

Posted - 2009-05-06 : 07:41:13

Hi Sakets 2000, i dont think your query solve my problem. =(

You see, I am hoping to get the result that returns the equipment type as the column.

Ind_id TV Laptop
======= ======== =========

not

Ind_Id Equipment
====== ==========

Thanks though!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-06 : 07:43:33

use PIVOT then.
here, http://msdn.microsoft.com/en-us/library/ms177410.aspx

oceanboy
Starting Member

44 Posts

Posted - 2009-05-06 : 23:21:29

hi sakets_2000,

i am not sure whether pivot will help me because i am not doing any aggregation. just purely display the "amount" of the equipment ie TV, laptop by the side of the Ind_ID, to show that whether that individual actually owns a TV/ Laptop

oceanboy
Starting Member

44 Posts

Posted - 2009-05-07 : 06:01:19

sorry Sakets_2000, Pivot somehow solves my problem! Thank you for that.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 08:56:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources