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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Cross tab Query or Some thing else
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

naz@ukchoice
Starting Member

3 Posts

Posted - 03/13/2006 :  09:51:48  Show Profile  Reply with Quote
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


Edited by - naz@ukchoice on 03/13/2006 10:02:30

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/13/2006 :  09:55:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

naz@ukchoice
Starting Member

3 Posts

Posted - 03/13/2006 :  10:35:00  Show Profile  Reply with Quote
Thanx for reply Madhivanan
Excellent Article. This is the thing I was looking for.
Go to Top of Page

naz@ukchoice
Starting Member

3 Posts

Posted - 03/13/2006 :  12:31:40  Show Profile  Reply with Quote
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



Go to Top of Page

sweta.jha
Starting Member

2 Posts

Posted - 03/29/2006 :  01:12:37  Show Profile  Reply with Quote
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
Go to Top of Page

sweta.jha
Starting Member

2 Posts

Posted - 03/29/2006 :  01:59:38  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/29/2006 :  02:19:28  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000