sql 2005 get back answers in Pivot format

On a simple select I am getting back answers on individual rows but I'd like the answer on ONE row only

select ' ClientName' as Company, SettingName  , SettingValue
from DB_Client.dbo.settings
where DB_Client.dbo.settings.settingname in ('PollingFequency','PollingTime','PollingFrequencyMinutes')

Company            SettingName                  SettingValue
 ClientName      PollingFequency                  Daily
 ClientName      PollingTime                  03/10/2011 23:59:00
 ClientName      PollingFrequencyMinutes            60


How do I get

Company            PollingFrequency      PollingTime            PollingFrequencyMinutes
ClientName      Daily                  03/10/2011 23:59:00      60
Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simone BSenior E-Commerce AnalystCommented:
How about somethng like this:

Select A.ClientName, A.PollingFrequency, B.PollingTime, C.PollingFrequencyMinutes from
(Select ClientName, SettingValue as PollingFrequency  from DG_Client.dbo.settings where SettingName = 'PollingFrequency') A
inner join
(Select ClientName, SettingValue as PollingTime  from DG_Client.dbo.settings where SettingName = 'PollingTime') B
on A.clientname = b.clientname
inner join
(Select ClientName, SettingValue as PollingFrequencyMinutes  from DG_Client.dbo.settings where SettingName = 'PollingFrequencyMinutes') C
on A.clientname = c.clientname
0
JestersGrindCommented:
Try this.

SELECT * FROM (
SELECT Company, SettingName  , SettingValue
FROM DB_Client.dbo.settings
WHERE DB_Client.dbo.settings.settingname in ('PollingFequency','PollingTime','PollingFrequencyMinutes')) AS a
PIVOT (MAX(SettingValue) FOR SettingName IN([PollingFequency], [PollingTime], [PollingFrequencyMinutes])) AS b

Greg
0
Chris MichalczukConsultantAuthor Commented:
getting a COLLATE error in this but the solution works
where would I add the collation areas
I would need to use this "collate SQL_Latin1_General_CP850_CI_AI" somewhere in each union all statement!!!!

SELECT * FROM (
SELECT 'TEST1' as Company, SettingName  , SettingValue
FROM odyssey_aah.dbo.settings
WHERE odyssey_aah.dbo.settings.settingname in ('TerminalTransferServerName','PollingFequency','PollingTime','PollingFrequencyMinutes')) AS a
PIVOT (MAX(SettingValue) FOR SettingName IN([TerminalTransferServerName ],[PollingFequency], [PollingTime], [PollingFrequencyMinutes])) AS b

Union All

SELECT * FROM (
SELECT 'Airport' as Company, SettingName  , SettingValue
FROM Odyssey_Airport.dbo.settings
WHERE Odyssey_Airport.dbo.settings.settingname in ('TerminalTransferServerName','PollingFequency','PollingTime','PollingFrequencyMinutes')) AS a
PIVOT (MAX(SettingValue) FOR SettingName IN([TerminalTransferServerName ],[PollingFequency], [PollingTime], [PollingFrequencyMinutes])) AS b

0
JestersGrindCommented:
I take it that odyssey_aah and Odyssey_Airport have different collations.  You are probably only going to need to address the collation in the second query, but just to be safe I added it to both in the code below.

Greg


SELECT * FROM (
SELECT 'TEST1' as Company, 
	SettingName collate SQL_Latin1_General_CP850_CI_AI AS SettingName, 
	SettingValue collate SQL_Latin1_General_CP850_CI_AI AS SettingValue
FROM odyssey_aah.dbo.settings
WHERE odyssey_aah.dbo.settings.settingname collate SQL_Latin1_General_CP850_CI_AI 
	in ('TerminalTransferServerName','PollingFequency','PollingTime','PollingFrequencyMinutes')) AS a
PIVOT (MAX(SettingValue) FOR SettingName IN([TerminalTransferServerName ],[PollingFequency], [PollingTime], [PollingFrequencyMinutes])) AS b

Union All

SELECT * FROM (
SELECT 'Airport' as Company, 
	SettingName collate SQL_Latin1_General_CP850_CI_AI AS SettingName, 
	SettingValue collate SQL_Latin1_General_CP850_CI_AI AS SettingValue
FROM Odyssey_Airport.dbo.settings
WHERE Odyssey_Airport.dbo.settings.settingname collate SQL_Latin1_General_CP850_CI_AI
in ('TerminalTransferServerName','PollingFequency','PollingTime','PollingFrequencyMinutes')) AS a
PIVOT (MAX(SettingValue) FOR SettingName IN([TerminalTransferServerName ],[PollingFequency], [PollingTime], [PollingFrequencyMinutes])) AS b

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.