need help for a query please :*)

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 help for a query please :*)

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-09-10 : 07:05:23

hi guys i have this table ex

id value count
1 x 1
1 y 2
2 x 3
2 y 1
3 x 2
4 y 3

i want a summary that will display the result as

id value count
1 x 1
1 y 2
2 x 3
2 y 1
3 x 2
3 y 0
4 x 0
4 y 3

i.e to show additional row if x is missing or y is missing and to set the count to 0
is it possible to display this result using a view or stored procedure?/
Thnks much

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-10 : 08:14:27

create table test (col0 varchar(10), col1 varchar(10), col2 int )
insert into test values ('A','x',1)
insert into test values ('A','x',1)
insert into test values ('A','y',1)
insert into test values ('B','x',1)

something like this :

select
AB.col0,
AB.col1,
sum(isnull(test.col2,0)) from

(select * from
(select distinct col0 from test)A
cross join
(select distinct col1 from test)B
)AB
left join test on test.col0=AB.col0 and test.col1=AB.col1
group by AB.col0,AB.col1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 08:33:37

[code]CREATE VIEW dbo.vwMyView
AS

SELECT s1.ID,
s2.Value,
COALESCE(s0.Cnt, 0) AS Cnt
FROM (
SELECT DISTINCT
ID
FROM @Sample
) AS s1
CROSS JOIN (
SELECT DISTINCT
Value
FROM @Sample
) AS s2
LEFT JOIN @Sample AS s0 ON s0.ID = s1.ID
AND s0.Value = s2.Value[/code]

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

ann06
Posting Yak Master

171 Posts

Posted - 2008-09-11 : 08:33:32

Thanks alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources