Cross tab Query or Some thing else

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

Site Related Forums

Article Discussion

Cross tab Query or Some thing else

Author

Topic

naz@ukchoice
Starting Member

3 Posts

Posted - 2006-03-13 : 09:51:48

Hi All,
I m having some problems with query, dont know how to get this result in sql server 2000.

I have tables show below:

table 1 Manufacturer

ManfID, ManfDesc
1 , Nokia
2 , Motorola

==============

table 2 : Handset

HandsetId, ModelDesc , ManfId
1 , 6230i , 1
2 , V3 , 2

=================

table 3 : Features

featureId , FeatureDesc
1 , Size
2 , Talk time
3 , Bluetooth

=============

table 4 : handsetFeatures

HandsetId, featureId, FeatureValue
1 , 1 , 64 x 24
1 , 2 , 5 Hours
1 , 3 , Yes
2 , 1 , 50 x 25
2 , 3 , Yes

I want to get results like this

Handset Name , size , Talk time , Bluetooth
Nokia 6230i , 64 x 24 , 5 Hours , Yes
Motorola V3 , 50 x 25 , N/A , yes

Any query please

Thanx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-13 : 09:55:19

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

naz@ukchoice
Starting Member

3 Posts

Posted - 2006-03-13 : 10:35:00

Thanx for reply Madhivanan
Excellent Article. This is the thing I was looking for.

naz@ukchoice
Starting Member

3 Posts

Posted - 2006-03-13 : 12:31:40

Hi All,

Its the sime kind of situation again but i would like to get resutls
in select statement (tabular Format)

and each value have its own column header like
shown below:-

Handset Name       Size       talktime
Nokia 6230i           64 x 24          5 Hours
Motorola V3           50 x 25          N/A

Cheers

sweta.jha
Starting Member

2 Posts

Posted - 2006-03-29 : 01:12:37

You need to manipulate the query below lil more in order to get data in desired format.

select A.HandsetName,A.Size,A.TalkTime,A.Bluetooth from
(select ManfDesc + ' ' + ModelDesc as HandsetName,
(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'Size' then HF.FeatureValue else null end) as Size,
(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'TalkTime' then HF.FeatureValue else null end)as TalkTime,
(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'Bluetooth' then HF.FeatureValue else null end) as Bluetooth
from Manufacturer M inner join Handset H
on M.ManfId = H.ManfId
Inner Join
HandsetFeatures HF
on HF.HandsetId = H.HandsetId
Inner Join
Features F
on HF.FeatureId = F.FeatureId
)A

sweta.jha
Starting Member

2 Posts

Posted - 2006-03-29 : 01:59:38

A better one over the last reply

select ManfDesc + ' ' + ModelDesc as HandsetName,
Max(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'Size' then HF.FeatureValue else null end) as Size,
isnull(Max(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'TalkTime' then HF.FeatureValue else null end),'N/A')as TalkTime,
Max(case when HF.FeatureId = F.FeatureId and F.FeatureDesc = 'Bluetooth' then HF.FeatureValue else null end) as Bluetooth
from Manufacturer M inner join Handset H
on M.ManfId = H.ManfId
Inner Join
HandsetFeatures HF
on HF.HandsetId = H.HandsetId
Inner Join
Features F
on HF.FeatureId = F.FeatureId
group by ManfDesc + ' ' + ModelDesc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 02:19:28

quote:
Originally posted by naz@ukchoice

Hi All,

Its the sime kind of situation again but i would like to get resutls
in select statement (tabular Format)

and each value have its own column header like
shown below:-

Handset Name Size talktime
Nokia 6230i 64 x 24 5 Hours
Motorola V3 50 x 25 N/A

Cheers

Why do you want to do this in T-SQL ? It can be done easily in your front end application

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources