asked on

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

SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JestersGrind

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

Chris Michalczuk

ASKER

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

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial