Need Tricky Select Query - 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

Need Tricky Select Query

Author

Topic

jazzyb
Starting Member

20 Posts

Posted - 2011-12-12 : 10:03:39

Respected Friends,

I am using MS SQL Express 2005

I have table "payroll" with category column (There are four A,B,C,D categories possible in this column)

my data is following ;
category::: month ::: deduction
A ::: January ::: 100
B ::: January ::: 70
A ::: February ::: 100
B ::: February ::: 50
C ::: March ::: 40

I want output as following :

month ::: category :::: deduction
January ::: A ::: 100
January ::: B ::: 70
January ::: C ::: --
January ::: D ::: ---

February ::: A ::: 100
February ::: B ::: 50
February ::: C ::: --
February ::: D ::: -

March ::: A ::: --
March ::: B ::; --
March ::: C ::: 40
March ::; D :;; -

I have tried Right Outer Join by creating follwing dummy table :

To get desired output I made dummy table with category column:

Col_category
A
B
C
D

I used command as following :

SELECT p.month, p.deduction, dummy.Col_category (SELECT month, deduction, category FROM payroll) As p RIGHT OUTER JOIN dummy ON p.category = dummy.Col_category

This is not displaying All possible Categories for each month.

Please suggest query for getting desired output.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:10:05

[code]
SELECT m.*,p1.deduction
FROM
(
SELECT d.Col_Category,p.month
FROM dummy d
CROSS JOIN (SELECT DISTINCT month FROM payroll) p
)m
LEFT JOIN payroll p1
ON p1.category = m.category
AND p1.month = m.month
[/code]

this will make sure you get NULL for missing values. then in your front application change it whatever you want like -- etc

doing it in sql will require unnecessary casting operations

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-13 : 03:00:21

[code]-- SwePeso 1
SELECT m.[Month],
c.Category,
w.Deduction
FROM (
SELECT DISTINCT [Month]
FROM dbo.YourTableNameHere
) AS m
CROSS JOIN (
SELECT DISTINCT Category
FROM dbo.YourTableNameHere
) AS c
LEFT JOIN dbo.YourTableNameHere AS w ON w.[Month] = m.[Month]
AND w.Category = c.Category

-- SwePeso 2
SELECT m.[Month],
c.Category,
w.Deduction
FROM (
SELECT 'January' AS [Month] UNION ALL
SELECT 'February' UNION ALL
SELECT 'March' UNION ALL
SELECT 'April' UNION ALL
SELECT 'May' UNION ALL
SELECT 'June' UNION ALL
SELECT 'July' UNION ALL
SELECT 'August' UNION ALL
SELECT 'September' UNION ALL
SELECT 'October' UNION ALL
SELECT 'November' UNION ALL
SELECT 'December'
) AS m
CROSS JOIN (
SELECT 'A' AS Category UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'
) AS c
LEFT JOIN dbo.YourTableNameHere AS w ON w.[Month] = m.[Month]
AND w.Category = c.Category[/code]

N 56°04'39.26"
E 12°55'05.63"

jazzyb
Starting Member

20 Posts

Posted - 2011-12-13 : 06:43:25

Respected,

Visakh16 & SwePeso,

Thanks for suggesting such a wonderful query, This has saved my many days of time, Actually I was strugling from 3 days to get desired output.

Cross Join suggested by you have made it magically.

Thanks again,

You are genius...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 06:53:00

quote:
Originally posted by jazzyb

Respected,

Visakh16 & SwePeso,

Thanks for suggesting such a wonderful query, This has saved my many days of time, Actually I was strugling from 3 days to get desired output.

Cross Join suggested by you have made it magically.

Thanks again,

You are genius...

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources