[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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
0
Chris Michalczuk
Asked:
Chris Michalczuk
  • 2
2 Solutions
 
Simone BCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now