Table 1(Box,primk,...)
Box
1
1
1
2
2
2
2
2
3
3
...........
..........

Table 2(Equipment,primk,...)
Equipment
Thimble
Thimble
Needle
Taffeta
Needle
Black spool
Scissors
Pattern
Pattern
Ruler
Thimble
Scissors
….
….

Box	Equipment
1	Thimble
1	Thimble
1	Needle
1	Taffeta
2	Needle
2	Black spool
2	Scissors	
2	Pattern
2	Pattern
2	Ruler
3	Thimble
3	Scissors


What i want to know is the number of tools in each box (with SQL Server and without Excel)
In other words, what I want is the following


Box	Thimble	Needle	Taffeta		
1	2	      1	       1		
Box	Needle	Black spool	Scissors	Pattern	Ruler
2	1	      1	          1	         2	       1
Box	Thimble	Scissors		
3	1	             1		


Or


Box	Thimble	Needle	Taffeta	Black spool Scissors Pattern	Ruler
1	2	      1	       1				
2		         1		               1	       1	   2	   1
3 1	                                   				                		


In other words, i'd like to know the number of occurrences by box .

If I use this query:
select count (equipment)equipmentnumber,box numberbox
from Table 1 a ,Table 2 b
where a.primk=b.primk
and equipment='scissors'
group by equipment


I have this result
NumberBox	NumberEquipment
2	           1
3	           1



If I use the following query

[code]select count (equipment)equipmentnumber,box numberbox
from Table 1 a ,Table 2 b
where a.primk=b.primk
and equipment='scissors'
group by equipment
union
select count (equipment)equipmentnumber,box numberbox
from Table 1 a ,Table 2 b
where a.primk=b.primk
and equipment='thimble'
group by equipment

quote:

---

Originally posted by visakh16

SELECT Box,
SUM(CASE WHEN Equipment='Thimble' THEN 1 ELSE 0 END) AS Thimble,
SUM(CASE WHEN Equipment='Needle' THEN 1 ELSE 0 END) AS Needle,
SUM(CASE WHEN Equipment='Taffeta' THEN 1 ELSE 0 END) AS Taffeta,
SUM(CASE WHEN Equipment='Black spool' THEN 1 ELSE 0 END) AS [Black spool],
SUM(CASE WHEN Equipment='Scissors' THEN 1 ELSE 0 END) AS Scissors,
SUM(CASE WHEN Equipment='Pattern' THEN 1 ELSE 0 END) AS Pattern,
SUM(CASE WHEN Equipment='Ruler' THEN 1 ELSE 0 END) AS Ruler
FROM Table1 t1
JOIN Table2 t2
ON t2.primk=t1.primk
GROUP BY Box

---

quote:

---

Originally posted by visakh16

and if you want to dynamically determine the equipments to be returned use

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

---

quote:

---

Originally posted by Van Helsing

Hi
I tried your query visakh16 but it doesn'?t work. The system returned me the two following error messages:
Error 156: Incorrect syntax near the keyword 'CASE'
Error:16945 The cursor was not declared

If i use (select box), then i have:
Error 170: Incorrect syntax near the keyword 'SUM'

Maybe, it'?s my fault. I didn'?t specify the type of variable: varchar for box and equipment but I don't have a syntax error converting varchar value 'value' to a column of datatype int or something else.

kunal.mehta, thanks but i don't understand your query.I'm afraid of changing my database.

---

quote:

---

Originally posted by visakh16

can you post your used query? the query i gave is working fine for me.

---

quote:

---

Originally posted by visakh16

still you didnt post the actual query used

---

quote:

---

Originally posted by tosscrosby

quote:

---

Originally posted by visakh16

still you didnt post the actual query used

---

From your post on 4/15 @ 11:06, your received an error about a cursor. Visakh's code does not have a cursor in it. If you cannot supply the actual code you are running you are not going to receive any accurate assistance. Visakh is really making a simple request in order to help YOU with a resolution.

Terry

-- Procrastinate now!

---

select a.box,
coalesce(sum(case when b.Equipment = 'Needle' then 1 end), 0) as numberofneedles,
coalesce(sum(case when b.Equipment = 'Thimble'  then 1 end), 0) as numberofthimbles,
coalesce(sum(case when b.Equipment = 'Scissors'  then 1 end), 0) as numberofScissors
.....
    from table 1 a inner join table 2 b ON a.primk = b.primk
group by boxes a.box

   SELECT a.box
		, SUM(CASE WHEN b.Equipment = 'Needle'		THEN 1 ELSE 0 END) AS numberofneedles
		, SUM(CASE WHEN b.Equipment = 'Thimble'		THEN 1 ELSE 0 END) AS numberofthimbles
		, SUM(CASE WHEN b.Equipment = 'Scissors'	THEN 1 ELSE 0 END) AS numberofScissors
.....
      FROM table 1 a 
INNER JOIN table 2 b 
		ON a.primk = b.primk
  GROUP BY a.boxes -- a.caisse ????? Whats this ??????

quote:

---

Originally posted by X002548

hey...don't need coalesce

   SELECT a.box
		, SUM(CASE WHEN b.Equipment = 'Needle'		THEN 1 ELSE 0 END) AS numberofneedles
		, SUM(CASE WHEN b.Equipment = 'Thimble'		THEN 1 ELSE 0 END) AS numberofthimbles
		, SUM(CASE WHEN b.Equipment = 'Scissors'	THEN 1 ELSE 0 END) AS numberofScissors
.....
      FROM table 1 a 
INNER JOIN table 2 b 
		ON a.primk = b.primk
  GROUP BY a.boxes -- a.caisse ????? Whats this ??????

Sorry for this mistake.I mean a.box

and what is a.caisse



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

select box "Boxnumber",
case when Equipment='Thimble' and Equipment='Needle' then 1
end
from Table 1 a,Table 2 b
where a.primk=b.primk
group by Box,Equipment

select distinct Box
from Table 1 a,Table 2 b
where a.primk1=b.primk
and Equipment='Needle'
and exists
(select distinct Box
from Table 1 a,Table 2 b
where a.primk=b.primk
and Equipment='Thimble'

quote:

---

Originally posted by visakh16

select box "Boxnumber"
from Table 1 a
join Table 2 b
on a.primk=b.primk
and Equipment in ('Thimble','Needle')
group by Box
having count(distinct Equipment)=2

---