Query to pick columns from multiple tables
-
Hi,
I have 2 tables. Table1 and Table2.
Table1 contains
ID Name Address
1 A XYZ
2 B ABC
3 C RST
Table2 Contains ---
ID Salary Month Year
1 2000 01 2006
1 3000 04 2006
3 5000 10 2006
3 3000 06 2006
1 4000 11 2006
2 8000 07 2006
I want to write a query that gives the o/p in the following format--
Quaterly1(1st four months),Quaterly2(2nd four months) and Quaterly 3(3rd four months) are salaries
ID Name Year Quaterly1 Quaterly2 Quaterly3
1 A 2006 5000 0 4000
2 B 2006 0 8000 0
3 C 2006 0 3000 5000
Any help will be highly appreciated.
Thanks,
Ankur
-
You need cross-tab query and a temp table as follows:
SET NOCOUNT ON
-- Drop tables if alraedy exist
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TmpTable3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TmpTable3]
-- Creat table Table1 and populate data
Create Table [dbo].Table1 ([id] int null,[Name] Varchar(100) NULL,[Address] Varchar(100) NULL)
Insert Into [dbo].Table1
SELECT 1, 'A', 'XYZ' UNION ALL
SELECT 2, 'B', 'ABC' UNION ALL
SELECT 3, 'C', 'RST'
-- Creat table Course and populate data
Create Table [dbo].Table2 (ID int null, salary float NULL, [Month] Varchar(10) NULL, [Year] Varchar(10) NULL)
Insert Into [dbo].Table2
SELECT 1,2000,'01','2006' UNION ALL
SELECT 1,3000,'04','2006' UNION ALL
SELECT 3,5000,'10','2006' UNION ALL
SELECT 3,3000,'06','2006' UNION ALL
SELECT 1,4000,'11','2006' UNION ALL
SELECT 2,8000,'07','2006'
-- Creat table TmpTable3 and populate data
Create Table [dbo].TmpTable3 ([id] int null,[Name] Varchar(100) NULL,salary float NULL, [Year] Varchar(10) NULL, [Quarter] Varchar(10) NULL)
Insert into TmpTable3
Select [dbo].Table1.[ID], [dbo].Table1.[Name], [dbo].Table2.Salary,[dbo].Table2.[Year],
Case
When Cast([dbo].Table2.[Month] as int) <= 4 Then '01'
When Cast([dbo].Table2.[Month] as int) <= 8 Then '02'
When Cast([dbo].Table2.[Month] as int) <= 12 Then '03'
End As [Quarter]
From [dbo].Table1 Join [dbo].Table2
On ([dbo].Table1.[ID]=[dbo].Table2.[ID])
Select * From [dbo].TmpTable3
DECLARE @Quarter Varchar(50)
DECLARE @SQL Varchar(5000)
DECLARE @SQL2 Varchar(5000)
DECLARE @Num INT
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT [Quarter] FROM [dbo].TmpTable3
GROUP BY [Quarter]
ORDER BY [Quarter]
SET @SQL = ''
SET @SQL2 = ''
SET @Num = 0
OPEN Fields
FETCH NEXT FROM Fields INTO @Quarter
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1
SET @SQL = 'SUM(CASE WHEN ISNULL(CONVERT(varchar(100),[Quarter]),''NA'') = ''' + @Quarter + ''' THEN [Salary] ELSE NULL END) As [Quarter' + @Quarter + ']
'
ELSE
SET @SQL = @SQL + ', SUM(CASE WHEN ISNULL(CONVERT(varchar(100),[Quarter]),''NA'') = ''' + @Quarter + ''' THEN [Salary] ELSE NULL END) As [Quarter' + @Quarter + ']
'
FETCH NEXT FROM Fields INTO @Quarter
END
CLOSE Fields DEALLOCATE Fields
SET @SQL = 'SELECT [ID],[Name],[Year],' + @SQL + '
FROM [dbo].TmpTable3
GROUP BY [ID],[Name],[Year]
ORDER BY [ID],[Name],[Year]
'
--PRINT @SQL
EXEC (@SQL)
-
No, no... Nice try, Terry, but I'm sorry - you really don't need cursor and dynamic SQL for that. The query is pretty simple and you can run it directly from the 2 original tables - you don't even need Table3. With your permission, I'll repost the table and data definitions for the 2 tables from your post and just add the final query.
-- Create table Table1 and populate data
CREATE TABLE Table1 ([id] int null,[Name] Varchar(100) NULL,[Address] Varchar(100) NULL)
INSERT INTO Table1
SELECT 1, 'A', 'XYZ' UNION ALL
SELECT 2, 'B', 'ABC' UNION ALL
SELECT 3, 'C', 'RST'
-- Creat table Table2 and populate data
CREATE TABLE Table2 (ID int null, salary float NULL, [Month] Varchar(10) NULL, [Year] Varchar(10) NULL)
INSERT INTO Table2
SELECT 1,2000,'01','2006' UNION ALL
SELECT 1,3000,'04','2006' UNION ALL
SELECT 3,5000,'10','2006' UNION ALL
SELECT 3,3000,'06','2006' UNION ALL
SELECT 1,4000,'11','2006' UNION ALL
SELECT 2,8000,'07','2006'
-- This is the query to get the result directly
SELECT t1.[ID], t1.[Name], t2.[Year],
SUM(CASE WHEN t2.[Month] IN ('01','02','03','04') THEN t2.salary ELSE 0 END) as Quarter1,
SUM(CASE WHEN t2.[Month] IN ('05','06','07','08') THEN t2.salary ELSE 0 END) as Quarter2,
SUM(CASE WHEN t2.[Month] IN ('09','10','11','12') THEN t2.salary ELSE 0 END) as Quarter3
FROM Table1 t1
JOIN Table2 t2 ON t1.[ID]=t2.[ID]
GROUP BY t1.[ID], t1.[Name], t2.[Year]
DROP TABLE Table1
DROP TABLE Table2
It is just a matter of taste whether to use conversion for the months or not, but since in this case I would have to include 2 conditions in definition of Q2 and Q3 anyway (like Month < 9 AND Month > 4), I decided I will just name the values explicitly.
-
... and another way how to write it, which can come handy in some situations, where the calculations are more complicated than a simple SUM - by separating the CASE and the calculation into two steps:
SELECT Q.[ID], Q.[Name], Q.[Year], SUM(Q.Q1) as Quarter1, SUM(Q.Q2) as Quarter2, SUM(Q.Q3) as Quarter3
FROM
(SELECT t1.[ID], t1.[Name], t2.[Year],
CASE WHEN t2.[Month] IN ('01','02','03','04') THEN t2.salary ELSE 0 END as Q1,
CASE WHEN t2.[Month] IN ('05','06','07','08') THEN t2.salary ELSE 0 END as Q2,
CASE WHEN t2.[Month] IN ('09','10','11','12') THEN t2.salary ELSE 0 END as Q3
FROM Table1 t1
JOIN Table2 t2 ON t1.[ID]=t2.[ID]) as Q
GROUP BY Q.[ID], Q.[Name], Q.[Year]
-
You are quite right. I am learning something small or big everyday. I was in the middle of inventing SQL crosstab reports tools. And I came up with the lengthy solution. Thanks for your kind advice.
-
Hi ,
Use the following query!! It's working..
SELECT a.e_id,a.name,a.address,
(case when datepart(qq,dt)=1 then sum(salary) else 0 end) QT_1 ,
(case when datepart(qq,dt)=2 then sum(salary) else 0 end) QT_2 ,
(case when datepart(qq,dt)=3 then sum(salary) else 0 end) QT_3 ,
(case when datepart(qq,dt)=4 then sum(salary) else 0 end) QT_4
FROM TAB_EMP a
LEFT OUTER join
(SELECT *,Convert(datetime, convert(varchar,convert(varchar,yr) + '-' + mon + '-' + '01')) dt
FROM TAB_SAL) b
on a.e_id=b.e_id
GROUP BY a.e_id,a.name,a.address,dt
Regards ,
Amit Gupta
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply