select distinct names with hierarchical data

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 2005 Forums

Transact-SQL (2005)

select distinct names with hierarchical data

Author

Topic

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-02-03 : 05:08:34

Hi All,

Im using sql server 2005 in my project.
I have 3 tables, names are Control, IssueControl, Risk
Control Table
ControlId--Name-- Parent
1 ---- Exa --- NULL
2 ---- adsa -- 1
3 ---- hjk --- 2
4 ---- uwujs --3
5 ---- hkfd --- 3
6 ---- jjs ---- 2
7 ---- jifd -- 4
8 ---- kjsi -- 5
9 --- iud --- 6
10 --- jfie ---6

IssueControl Table
ControlId RiskId
2 1
3 2
2 3
4 1
5 2

Risk Table
RiskId --- Name
1 ----- High
2 ----- Medium
3 ----- Low

This is my query:

WITH REPORTS1(ControlId, Parent, Name, IssueId, Low, Medium, High)
AS
(
SELECT Control.ControlId, Parent, Control.Name,
COUNT ( DISTINCT Kaon.Issue.IssueId) AS IssueId,
COUNT ( DISTINCT CASE WHEN Risk.Name = 'Low' THEN Risk.RiskId END ) AS Low,
COUNT ( DISTINCT CASE WHEN Risk.Name = 'Medium' THEN Risk.RiskId END ) AS Medium,
COUNT ( DISTINCT CASE WHEN Risk.Name = 'High' THEN Risk.RiskId END ) AS High
FROM Control LEFT OUTER JOIN IssueControl ON Control.ControlId=IssueControl.ControlId
LEFT OUTER JOIN Risk ON Risk.RiskId=Issue.RiskId
GROUP BY Control.ControlId, ControlId, Control.Name
),
REPORTS2
AS
(
SELECT ControlId AS Control1, ControlId AS Control2, Name, IssueId,
SUM ( Low ) AS Low, SUM ( Medium ) AS Medium, SUM ( High ) AS High
FROM REPORTS1 GROUP BY ControlId, Name, IssueId
UNION ALL
SELECT R2.Control1, R1.ControlId, R1.Name, R1.IssueId , R1.Low, R1. Medium, R1.High
FROM REPORTS2 R2 JOIN REPORTS1 R1 ON R2.Control2=R1.Parent
)
SELECT Control1 AS ControlId, Name, SUM ( IssueId ) AS CountIssues, SUM ( Low ) AS Low, SUM ( Medium ) AS Medium,
SUM ( High ) AS High
FROM REPORTS2 GROUP BY Control1, Name

Now my problem is, in above query whe im going to add Name, im getting repeated values.
So please any one can help me...

Thanks,
VKR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:11:53

what should be the expected output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources