Please help me to write 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 2005 Forums

Transact-SQL (2005)

Please help me to write query

Author

Topic

Muhammad Nasir
Starting Member

10 Posts

Posted - 2011-02-04 : 00:42:24

Hello Friends

I need you expert help on the issue i am currently faceing
I have two tables with large amount of data. Below describes the structure of the tables

Clients
--------
ClientID, ClinetName, Address, ContactPerson
--------------------------------------
1 Mark Stephen

Client_Survey
-------------
Client_ID, Question_Number Question_Name, Answer
1 1 Mobile User Yes
1 2 Status Employee
1 3 Nationality USA

Now i want to write a query which could display the result as below

Client_ID,Name,Address,Contact_person,Nationality,Status,Mobile_User

My question is how would i do that ?

My approach is that i should write a scalar function to whome i pass two parameters (clinet_ID and QuestionNumber) and that function will return a scalar value which will eventually become a coloum of my report for example

Select *,dbo.fn_GetAnserFromSurver(1,2) as Status,
dbo.fn_GetAnserFromSurver(1,3) as Nationality

Now the proble is that i have a large amount of data and scalar functions are reducing my speed of query. Please help me with some solution

Thanxs and accept my appologies for the long post

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-04 : 01:21:21

Well first of all I do not see some of the required output columns (as highlighted below) in the original tables ..

Client_ID,Name,Address,Contact_person,Nationality,Status,Mobile_User

where are the coming from ?

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-04 : 01:57:36

Hi,
check it once ...

(I wrote this query for display your the expected result)

this may useful

create table #Clients
(
ClientID int,
ClinetName varchar(20),
Address varchar(20),
ContactPerson varchar(20)
)
go
insert into #Clients(ClientID,ClinetName,Address,ContactPerson)values(1,'Mark','usa',' Stephen')
insert into #Clients(ClientID,ClinetName,Address,ContactPerson)values(2,'Mark','usa',' Stephen')

go

create table #Client_Survey
(
Client_ID int,
Question_Number int,
Question_Name varchar(20),
Answer varchar(20)
)
go
insert #Client_Survey
select 1,1,'Mobile User ','Yes' union all
select 1,2,'Status',' Employee' union all
select 1,3,'Nationality','USA'union all
select 2,1,'Mobile User ','Yes' union all
select 2,2,'Status',' Employee'
go
select * from #Clients
select * from #Client_Survey
go
select
c.ClientID
,ClinetName
,Address
,ContactPerson
,min(Nationality) as Nationality
,min(Status)Status
,min([Mobile User ])[Mobile User ]
from #Clients c
join(
SELECT Client_ID,[Mobile User ],[Status],[Nationality]
FROM
(
SELECT Client_ID,Question_Number,Question_Name,Answer
FROM #Client_Survey
) AS source
PIVOT
(
min(Answer)
FOR Question_Name IN ([Mobile User ] ,[Status],[Nationality])
) as pvt
)Der on c.ClientID=Der.Client_ID
group by c.ClientID,ClinetName,Address,ContactPerson

result:
ClientID ClinetName Address ContactPerson Nationality Status Mobile User
1 Mark usa Stephen USA Employee Yes
2 Mark usa Stephen NULL Employee Yes

--Ranjit

Muhammad Nasir
Starting Member

10 Posts

Posted - 2011-02-06 : 19:45:56

Hi Ranjit

Thank you so much- You are wonderful

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-06 : 22:46:47

wel come

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources