How to prepare 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to prepare query

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2011-01-13 : 01:29:04

I am using sql server 2005, SSrs 2005

I am preparing a voting form where employee can nominate (from nomination table) a employee and fill the option (Good , avg , excelence) against the List of parameter

I have parameter tables (para_master)

Like

Parano Attributes

1 Comminication

2 Displine

3 Team Work

And Option master as

Opid Option

1 good

2 Avg

3 Excellence

Now I use to store record in two table One Hdr and other Details

Hdr

Year Nominatedby Nominee HdrID

2011 abc imran 1

2011 efg imran 2

2011 123 Marshal 3

And Details tables stores details of employee rated against the parameter

HdrID paraid optioned

1 1 3

1 2 2

1 3 3

2 1 3

2 2 3

2 3 1

3 1 3

3 2 3

3 3 3

Now I want ouput as

Imran (2 Nomination)

Parameter Good Avg excellence

1 0 0 2

2 0 1 1

3 1 0 1

Marshal(1 Nomination)

Parameter Good Avg excellence

1 0 0 1

2 0 0 1

3 0 0 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 13:05:57

[code]
SELECT p.Parano,
SUM(CASE WHEN o.Option = 'Good' THEN 1 ELSE 0 END) AS Good,
SUM(CASE WHEN o.Option = 'Avg' THEN 1 ELSE 0 END) AS Avg,
SUM(CASE WHEN o.Option = 'Excellence' THEN 1 ELSE 0 END) AS Excellence
FROM Hdr h
INNER JOIN Details d
ON d.optioned = h.HdrID
INNER JOIN Option o
ON o.Opid = d.optioned
INNER JOIN para_master p
ON p.Parano = d.paraid
WHERE h.Nominee = 'Imran'
GROUP BY p.Parano
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources