Crosstab 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

SQL Server Development (2000)

Crosstab Query

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2004-09-14 : 17:31:26

Hi GUys,

Below is my data....

EquipNum|TechType
________________
123 | $GXXX
123 | Hopy1

124 | $GXXX

125 | $GXXX
125 | Hopy2
125 | DXXX

126 | $TXXX

127 | $FXXX
127 | Hopy1

Below is the output i want...

Critera | GSM | TDMA

Hopy1 | 1 | 1
Hopy2 | 1 | 0

Rules:

1.We want to take into consideration only the TechTypes that start with '$' sign
2.if TechType starts with $G or $T then it is counted as "GSM" and all other Techtypes
which does not start with '$G' OR '$T' are considered as TDMA
3.Hopy1 and Hopy2 are the names of the criterias on which the crosstab is built.

4. In the output, to start with Hopy 1 - EquipNum 123 has Hopy1 so we look in the table
for EquipNum 123 and see it has any other TechTypes if so use the rules 1 and 2 to get the
count in the respective categories ie. either GSM or TDMA.

I hope i made myself clear.

Can someone please help me write this crosstab query.

Thanks

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-15 : 02:06:57

are Hopy1 and Hopy2 the only two crieteria...

does this help...

create view v_xyz as
select EquipNum, case left(TechType, 2) when '$G' then 'GSM' when '$T' then 'GSM' else 'TDMA' end TECH, count(*) cnt
from xyz
where TechType not in ('Hopy1', 'Hopy2')
and left(TechType, 1) = '$'
group by EquipNum, case left(TechType, 2) when '$G' then 'GSM' when '$T' then 'GSM' else 'TDMA' end

select A.TechType, sum(isnull(B.cnt, 0)) as GSM, sum(isnull(C.cnt, 0)) as TDMA
from xyz A, v_xyz B, v_xyz C
where A.TechType in ('Hopy1', 'Hopy2')
and A.EquipNum *= B.EquipNum
and A.EquipNum *= C.EquipNum
and B.TECH = 'GSM'
and C.TECH = 'TDMA'
group by A.TechType

Hemanth Gorijala
BI Architect / DBA

dupati1
Posting Yak Master

123 Posts

Posted - 2004-09-15 : 08:28:38

Wow, thanks for your reply...

I definitely need some time to understand your query :)

Yes there can be many criteria...does that change your query??

Thanks a lot for your reply.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 09:31:44

hgorijal --

Try to avoid *= joins in the WHERE clause -- use LEFT OUTER JOIN syntax, it is much more readable and SQL Server is likely to not support that syntax in future versions.

- Jeff

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-15 : 13:39:36

quote:
Originally posted by dupati1

Wow, thanks for your reply...

I definitely need some time to understand your query :)

Yes there can be many criteria...does that change your query??

Thanks a lot for your reply.

yes. this will only work for static "criteria" only; not for dynamic crieteria. Include them along with 'Hopy1' and 'Hopy2' in the view and the query.

Hemanth Gorijala
BI Architect / DBA...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-15 : 13:40:40

quote:
Originally posted by jsmith8858

hgorijal --

Try to avoid *= joins in the WHERE clause -- use LEFT OUTER JOIN syntax, it is much more readable and SQL Server is likely to not support that syntax in future versions.

- Jeff

Totally with you Jeff; am not sure what I was thining... ;-)

Hemanth Gorijala
BI Architect / DBA...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources