need help in count

Question

hi

i have 2 table

table A 

year month ID  count

2012  1     1     10

2012  2     1     15

2012  1    2      20

2012  10  1     30

Table B

id     name

1      xerox

2      paper

3     xyz

my out put shoudl be like this

name     month1       month2          month3

xerox    10                  15

paper    15

just like for every month 

how to do using stored procedure 

Answer 1

Hello,

  Try something as below

CREATE PROCEDURE GetMonthCount
as
SELECT TableB.Name 
	   ,Month1 = SUM(CASE WHEN TableA.MONTH = 1 THEN TableA.COUNT END)
	   ,Month2 = SUM(CASE WHEN TableA.MONTH = 2 THEN TableA.COUNT END)
	   ,Month3 = SUM(CASE WHEN TableA.MONTH = 3 THEN TableA.COUNT END)
  FROM TableA
  JOIN TableB 
    ON TableA.ID  = TableB.ID  

---

Best Regards Sorna

Answer 2

SELECT name,[1] AS month1,[2] AS month2
FROM   (SELECT a.[year],a.[month],b.name,a.[count]
        FROM   tableA
               INNER JOIN tableB b
                       ON a.ID = b.id) src
       PIVOT (MAX([count])
             FOR [month] IN ([1],[2] )) pvt 

Please do not use keywords as your object names!

Answer 3

I think you need to group by name

SELECT Table_2.Name 
	   ,Month1 = SUM(CASE WHEN Table_1.MONTH = 1 THEN Table_1.COUNT END)
	   ,Month2 = SUM(CASE WHEN Table_1.MONTH = 2 THEN Table_1.COUNT END)
	   ,Month3 = SUM(CASE WHEN Table_1.MONTH = 3 THEN Table_1.COUNT END)
  FROM Table_1
  JOIN Table_2 
    ON Table_1.ID  = Table_2.ID  
    group by table_2.name

Answer 4

DECLARE @tabA TABLE(Yr INT,Mnt INT,Id INT,Cnt INT)
INSERT INTO @tabA(Yr,Mnt,Id,Cnt)
VALUES  (2012,1,1,10),(2012,2,1,15),(2012,1,2,20),(2012,10,1,30)
SELECT * FROM  @tabA
DECLARE @tabB TABLE(Id INT,NAME VARCHAR(10))
INSERT INTO @tabB(Id,Name)
VALUES  (1,'xerox'),(2,'paper'),(3,'xyz')
SELECT * FROM  @tabB
SELECT NAME,[1] AS Month1,[2] AS Month2,[10] AS Month10
FROM(SELECT b.NAME,a.Mnt,a.Cnt,a.Id
		  FROM @tabA a
				INNER JOIN @tabB b
					ON a.Id = b.Id)src
PIVOT(MAX(Cnt) FOR Mnt IN([1],[2],[10])) AS pv
ORDER BY Id

---

Narsimha