Ppivoting result set for every application value

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

Transact-SQL (2000)

Ppivoting result set for every application value

Author

Topic

t01621
Starting Member

2 Posts

Posted - 2008-10-02 : 14:19:31

Hi Everyone

This is my first post here so sorry if this isn't the correct place to post this kind of question

I am using the below query which displays all the pc's (concatenated into one field) that have a certain application installed, obviously i search for the certain application using @app
For example if I use the below query, searching for 'ms office 2000' it would display the following result ;

Application/ Installed on/ Total pc's
MS office 2000/ pc1, pc2, pc3,/ 3

The query I am using is;

declare @app varchar(255)
select @app = 'MS Office 2000'

declare @comp varchar( 8000 )
set @comp = ''
select @comp = @comp + cn.computer + ', '
from sainstalledapplications ia
join sacomputernames cn
on ia.computerid=cn.id
where application = @app

select distinct Application, @comp[Installed On], count(computerid)[Total pc's]
from sainstalledapplications
where application = @app
group by application

Now what I want this to do is go through all the applications field values and find the pc's that have that application installed instead of having to specify each application indivdually (there are over 300 applications and obviously this number will grow as people install more applications on their pc's)

Do I have to use a while loop to do this (if so how, never used one before) or is there a better way to format the query?

The tables have the following columns in case it is any help;

Tablename- sainstalledapplications;
columns - computerid, application

Tablename - sacomputernames
columns - id(this links to computerid), computer, domain

Really appreciate your help in advanced and if you need any more info please let me know
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 14:29:42

one method is to create a udf like this

CREATE FUNCTION GetInstalledPCs
(
@app varchar(255)
)
RETURNS varchar(8000) 
AS
declare @comp varchar( 8000 )
set @comp = ''
select @comp = @comp + cn.computer + ', ' 
from sainstalledapplications ia
join sacomputernames cn 
on ia.computerid=cn.id 
where application = @app
RETURN @comp
GO

and then use it like this

select m.Application,
dbo.GetInstalledPCs(m.Application),
m.[Total pc's] 
from
(
select Application
count(computerid)[Total pc's] 
from sainstalledapplications
group by application)m

t01621
Starting Member

2 Posts

Posted - 2008-10-03 : 04:38:49

Thank you so much for your reply, this worked really well. It runs in less than 1 second on another database i have with over 600 entries, i'll definitely be looking into using functions for other things too!

Just incase someone else wants to use this resolution i needed to add a begin and end to the function and in the select statement I needed to add a comma in between 'Application' and 'count(computerid)' -see below, thanks again visakh16

CREATE FUNCTION GetInstalledPCs
(@app varchar(255))
RETURNS varchar(8000)
AS
begin
declare @comp varchar( 8000 )
set @comp = ''
select @comp = @comp + cn.computer + ', '
from sainstalledapplications ia
join sacomputernames cn
on ia.computerid=cn.id
where application = @app

RETURN @comp
end
GO

select m.Application,
dbo.GetInstalledPCs(m.Application)[PC List],
m.[Total pc's]
from
(select Application, count(computerid)[Total pc's]
from sainstalledapplications
group by application)m

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 05:24:54

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources