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
 General SQL Server Forums
 Database Design and Application Architecture
 ???How to create a table like this???

Author  Topic 

shaoranwill
Starting Member

1 Post

Posted - 2007-09-04 : 09:30:24
I have now two tables, tb1, tb2.

Tb1
StationID Day No_In
1 2 90
1 5 80
1 4 10



Tb2
StationID Day No_Out
1 2 70
1 1 10
1 3 90
1 5 60



I want to create one table Tb3 (the order is not important)

StationID Day No_In No_Out
1 1 0 10
1 2 90 70
1 3 0 90
1 4 10 0
1 5 80 60




I don’t know how to write SQL. (I guess I should use JOIN)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 09:35:47
You are almost right!
-- Prepare sample data to mimic your environment
DECLARE @Tb1 TABLE (StationID INT, Day INT, No_In INT)

INSERT @Tb1
SELECT 1, 2, 90 UNION ALL
SELECT 1, 5, 80 UNION ALL
SELECT 1, 4, 10

DECLARE @Tb2 TABLE (StationID INT, Day INT, No_Out INT)

INSERT @Tb2
SELECT 1, 2, 70 UNION ALL
SELECT 1, 1, 10 UNION ALL
SELECT 1, 3, 90 UNION ALL
SELECT 1, 5, 60

-- Show the expected output
SELECT d.StationID,
d.Day,
SUM(d.No_In) AS No_In,
SUM(d.No_Out) AS No_Out
FROM (
SELECT StationID,
Day,
No_In,
0 AS No_Out
FROM @Tb1

UNION ALL

SELECT StationID,
Day,
0,
No_Out
FROM @Tb2
) AS d
GROUP BY d.StationID,
d.Day
ORDER BY d.StationID,
d.Day




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 09:40:17
Maybe a view is better suited for you?
CREATE VIEW dbo.vwTb3
AS

SELECT d.StationID,
d.Day,
SUM(d.No_In) AS No_In,
SUM(d.No_Out) AS No_Out
FROM (
SELECT StationID,
Day,
No_In,
0 AS No_Out
FROM Tb1

UNION ALL

SELECT StationID,
Day,
0,
No_Out
FROM Tb2
) AS d
GROUP BY d.StationID,
d.Day



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -