JOIN - 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

General SQL Server Forums

New to SQL Server Programming

JOIN

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-07-18 : 17:49:38

Hi I have the following table:
MONTH Status
------------ ---------
1 Successful
1 Failed
1 Canceled
2 Successful
2 Failed
2 Canceled
3 Successful
3 Failed
3 Canceled
4 Successful
4 Failed
4 Canceled
5 No Closure Stat
5 Successful
5 Failed
5 Canceled
5 Unsuccessful
6 Successful
6 Failed
6 Canceled
6 SWP
6 Unsuccessful
7 Successful
7 Failed
7 Canceled
7 SWP
7 Unsuccessful
8 No Closure Stat
9 No Closure Stat
10 No Closure Stat
11 Successful
12 No Closure Stat

As you can see, Status field can have 'Successful', 'Unsuccessful', 'Failed', 'Canceled' , 'SWP' and 'No Closure Stat' values. Now, I need this values for all month: The output that I need is as follows:
MONTH STATUS
----- ------
1 Successful
1 Unsuccessful
1 Failed
1 Canceled
1 SWP
1 No Closure Stat
2 Successful
2 Unsuccessful
2 Failed
2 Canceled
2 SWP
2 No Closure Stat
.
.
.

I know that it is possible with joins, but I couldn't figure it out.

Thank you a lot.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-18 : 18:18:23

Assuming that that table has months 1-12,then you could do something like this:

SELECT [Month], [Status]
FROM
(
	SELECT DISTINCT [Status]
	FROM MyTable
) AS Stat
CROSS JOIN
(	
	SELECT DISTINCT [Month]
	FROM MyTable
) AS Mon

mukhan85
Starting Member

46 Posts

Posted - 2008-07-18 : 19:02:02

Thank you it works great. Is it possible to extend that query so that it would give me the following result.

Table:
MONTH Status Number
---- --------- -----
1 Successful 3
1 Failed 7
1 Canceled 2
2 Successful 4
2 Failed 1
2 Canceled 3
3 Successful 6
3 Failed 7
3 Canceled 2
4 Successful .
4 Failed .
. . .
. . .
. . .

As you can see, Status field can have 'Successful', 'Unsuccessful', 'Failed', 'Canceled' , 'SWP' and 'No Closure Stat' values. Now, I need this values for all month: The output that I need is as follows:
MONTH STATUS NUMBER
----- ------ ------
1 Successful 3
1 Unsuccessful 0
1 Failed 7
1 Canceled 2
1 SWP 0
1 No Closure Stat 0
2 Successful 4
2 Unsuccessful 0
2 Failed 1
2 Canceled 3
2 SWP 0
2 No Closure Stat 0
.
.
.

I know that it is possible with joins, but I couldn't figure it out.

Thank you a lot.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-18 : 19:08:46

[code]SELECT
[Month],
[Status],
COALESCE(Number, 0) AS Number
FROM
(
SELECT [Month], [Status]
FROM
(
SELECT DISTINCT [Status]
FROM MyTable
) AS Stat
CROSS JOIN
(
SELECT DISTINCT [Month]
FROM MyTable
) AS Mon
) AS T
LEFT OUTER JOIN
MyTable
ON T.[Status] = MyTable.[Status]
AND T.[Month] = MyTable.[Month][/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources