SQL 2008 Pivot

The inner query has data
but when I pivot everything is null. What Am i doing wrong?

--sp_dbcmptlevel 'issPortal',90

Select 
arNumber
,CustomerName
,Descrip
,MAX(COALESCE([AdminServer], AdminServer)) AS 'AdminServer'
,ApplicationServer
,C10DatabaseName
,C10Server
,ReportNetURL
,C8URL
,C10URL
,C8DatabaseName
,C8Server
,Customs
,EnvironmentSetupDate
,Integration
,PortNumber
,ReportNetDatabaseName
,ReportNetServer
,SQLPassword
,SQLServer
,SSOIntegration
,UTADatabaseName
,VersionNumber
,WebServers
,WeblogicDomain
from 
( 
Select c.arNumber,c.CustomerName, cpa.Descrip
,case 
	When ltrim(rtrim(replace(tpa.FieldName,'?',''))) Like '%C10 URL%' Then 'C10URL' 
	ELSE ltrim(rtrim(replace(tpa.FieldName,'?','')))
end FieldName	--c.arNumber,c.CustomerName, cpa.Descrip,ltrim(rtrim(tpa.FieldName)),ct.FieldValue 
,isNUll(ct.FieldValue,'') FieldValue
from dbo.customerthirdpartyappfield ct (nolock) 
	Join (
			SELECT distinct CustomerThirdPartyAppID
				FROM   dbo.customerthirdpartyappfield (nolock) 
			WHERE  fieldvalue LIKE '%time%' 
			AND fieldvalue NOT LIKE '%wb61%' 
			AND fieldvalue NOT LIKE '%test%') a
		on  ct.CustomerThirdPartyAppID = a.CustomerThirdPartyAppID
	Join dbo.ThirdPartyAppField tpa
		on tpa.ThirdPartyAppFieldID = ct.ThirdPartyAppFieldID
	Join dbo.customerthirdpartyappfield cta (nolock) 
		on ct.CustomerThirdPartyAppID = cta.CustomerThirdPartyAppID
	Left Join customerthirdpartyapp	cpa (nolock)
		on cta.CustomerThirdPartyAppID = cpa.CustomerThirdPartyAppID
	Join Customer c (nolock)
		on c.CustomerID = cpa.CustomerID
) src		
Pivot ( MAX(FieldValue) for FieldName in ([AdminServer],[ApplicationServer],[C10DatabaseName],[C10Server],[C10URL],[C8DatabaseName],[C8Server],[C8URL],[Customs],[EnvironmentSetupDate],[Integration],[PortNumber],[ReportNetDatabaseName],[ReportNetServer],[ReportNetURL],[SQLPassword],[SQLServer],[SSOIntegration],[UTADatabaseName],[VersionNumber],[WebServers],[WeblogicDomain]) ) pvt
Group by 
arNumber
,CustomerName
,Descrip

,ApplicationServer
,C10DatabaseName
,C10Server
,ReportNetURL
,C8URL
,C10URL
,C8DatabaseName
,C8Server
,Customs
,EnvironmentSetupDate
,Integration
,PortNumber
,ReportNetDatabaseName
,ReportNetServer
,SQLPassword
,SQLServer
,SSOIntegration
,UTADatabaseName
,VersionNumber
,WebServers
,WeblogicDomain

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

chaauCommented:
PIVOT does not require a GROUP BY. Try this one:
--sp_dbcmptlevel 'issPortal',90

Select 
arNumber
,CustomerName
,Descrip
,AdminServer
,ApplicationServer
,C10DatabaseName
,C10Server
,ReportNetURL
,C8URL
,C10URL
,C8DatabaseName
,C8Server
,Customs
,EnvironmentSetupDate
,Integration
,PortNumber
,ReportNetDatabaseName
,ReportNetServer
,SQLPassword
,SQLServer
,SSOIntegration
,UTADatabaseName
,VersionNumber
,WebServers
,WeblogicDomain
from 
( 
Select c.arNumber,c.CustomerName, cpa.Descrip
,case 
	When ltrim(rtrim(replace(tpa.FieldName,'?',''))) Like '%C10 URL%' Then 'C10URL' 
	ELSE ltrim(rtrim(replace(tpa.FieldName,'?','')))
end FieldName	--c.arNumber,c.CustomerName, cpa.Descrip,ltrim(rtrim(tpa.FieldName)),ct.FieldValue 
,isNUll(ct.FieldValue,'') FieldValue
from dbo.customerthirdpartyappfield ct (nolock) 
	Join (
			SELECT distinct CustomerThirdPartyAppID
				FROM   dbo.customerthirdpartyappfield (nolock) 
			WHERE  fieldvalue LIKE '%time%' 
			AND fieldvalue NOT LIKE '%wb61%' 
			AND fieldvalue NOT LIKE '%test%') a
		on  ct.CustomerThirdPartyAppID = a.CustomerThirdPartyAppID
	Join dbo.ThirdPartyAppField tpa
		on tpa.ThirdPartyAppFieldID = ct.ThirdPartyAppFieldID
	Join dbo.customerthirdpartyappfield cta (nolock) 
		on ct.CustomerThirdPartyAppID = cta.CustomerThirdPartyAppID
	Left Join customerthirdpartyapp	cpa (nolock)
		on cta.CustomerThirdPartyAppID = cpa.CustomerThirdPartyAppID
	Join Customer c (nolock)
		on c.CustomerID = cpa.CustomerID
) src		
Pivot ( MAX(FieldValue) for FieldName in ([AdminServer],[ApplicationServer],[C10DatabaseName],[C10Server],[C10URL],[C8DatabaseName],[C8Server],[C8URL],[Customs],[EnvironmentSetupDate],[Integration],[PortNumber],[ReportNetDatabaseName],[ReportNetServer],[ReportNetURL],[SQLPassword],[SQLServer],[SSOIntegration],[UTADatabaseName],[VersionNumber],[WebServers],[WeblogicDomain]) ) pvt

Open in new window

BTW, I have removed the COALESCE([AdminServer], AdminServer), as it does not make sense. Use AdminServer
Leo TorresSQL DeveloperAuthor Commented:
Yea same result. I did the group by and the coalesce because I was desperate. The query you gave me does the same.
There is data in the inner query. That's what I had originally but I can figure out why  I am getting NULLS.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post some sample data?
FieldValue it's VARCHAR data type?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Leo TorresSQL DeveloperAuthor Commented:
sure, sample data and you can see NULLs for values that exists on the list same client notice the ARnumber.
EE-sample.xlsx
EE-samplePvt.bmp
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you want only to return one row from your SELECT statement and that row should be the one with the highest FieldValue, right?
Did you try a SELECT * instead of all those fields?
Select *
from 
( 
Select c.arNumber,c.CustomerName, cpa.Descrip
,case 
	When ltrim(rtrim(replace(tpa.FieldName,'?',''))) Like '%C10 URL%' Then 'C10URL' 
	ELSE ltrim(rtrim(replace(tpa.FieldName,'?','')))
end FieldName	--c.arNumber,c.CustomerName, cpa.Descrip,ltrim(rtrim(tpa.FieldName)),ct.FieldValue 
,isNUll(ct.FieldValue,'') FieldValue
from dbo.customerthirdpartyappfield ct (nolock) 
	Join (
			SELECT distinct CustomerThirdPartyAppID
				FROM   dbo.customerthirdpartyappfield (nolock) 
			WHERE  fieldvalue LIKE '%time%' 
			AND fieldvalue NOT LIKE '%wb61%' 
			AND fieldvalue NOT LIKE '%test%') a
		on  ct.CustomerThirdPartyAppID = a.CustomerThirdPartyAppID
	Join dbo.ThirdPartyAppField tpa
		on tpa.ThirdPartyAppFieldID = ct.ThirdPartyAppFieldID
	Join dbo.customerthirdpartyappfield cta (nolock) 
		on ct.CustomerThirdPartyAppID = cta.CustomerThirdPartyAppID
	Left Join customerthirdpartyapp	cpa (nolock)
		on cta.CustomerThirdPartyAppID = cpa.CustomerThirdPartyAppID
	Join Customer c (nolock)
		on c.CustomerID = cpa.CustomerID
) src		
Pivot ( MAX(FieldValue) for FieldName in ([AdminServer],[ApplicationServer],[C10DatabaseName],[C10Server],[C10URL],[C8DatabaseName],[C8Server],[C8URL],[Customs],[EnvironmentSetupDate],[Integration],[PortNumber],[ReportNetDatabaseName],[ReportNetServer],[ReportNetURL],[SQLPassword],[SQLServer],[SSOIntegration],[UTADatabaseName],[VersionNumber],[WebServers],[WeblogicDomain]) ) pvt

Open in new window

Christopher GordonSenior Developer AnalystCommented:
I only bring this up because of the sample data (may not be an issue)

Make sure that the field names you've specified in your PIVOT statement:
Pivot ( MAX(FieldValue) for FieldName in ([AdminServer],[ApplicationServer],[C10DatabaseName]...

Match the actual data in your "Field Value" field.  The only reason I mention it is that I noticed the following conditions:

1. [AdminServer] in your T-SQL will not match to the value 'Admin Server' in field value. (use [Admin Server] instead)
2. [ApplicationServer] in your T-SQL will not match to the value 'Application Server' in field value (use [Application Server] instead)

In short the field names need to match the field values.   Like I said, if this condition only exists in your sample data provided then u can ignore the post.
Christopher GordonSenior Developer AnalystCommented:
Here is as pivot sample based on your sample values.  You can just copy paste into SSMS.

declare @myTable table (
	arNumber varchar(250), 
	customerNumber varchar(250), 
	descrip varchar(250), 
	CustomerThirdPartyAppID varchar(250),
	FieldName varchar(250),
	FieldValue varchar(2100))

insert into @myTable values
('ACA1001',             	'Client',	'UTA 6.1',	'2D746E75-044D-411D-ABE7-56B7A653C4E2',	'SQL Server',	'Value1'),
('ACA1001',             	'Client',	'UTA 6.1',	'216CA9DB-167C-411F-BED9-2E42E03A7304',	'UTA Database Name',	'value2'),
('ACA1001',             	'Client',	'UTA 6.1',	'1680A742-652E-443B-84FE-E31FEF008485',	'C10 Server',	'value3'),
('ACA1001',             	'Client',	'UTA 6.1',	'3245EC89-C443-4901-911C-317D8389B1DB',	'Web Servers',	'value4'),
('ACA1001',             	'Client',	'UTA 6.1',	'1BBB5F1B-6EA9-43E2-9013-EEA2EB983220',	'Customs',	'Y'),
('ACA1001',             	'Client',	'UTA 6.1',	'2A903322-BE1C-45E7-B485-C52AA447262D',	'Version Number',	'6.1_2791_v5'),
('ACA1001',             	'Client',	'UTA 6.1',	'511A1607-9926-4ED2-A208-D82EB5F240CC',	'Environment Setup Date',	'6/4/2014'),
('ACA1001',             	'Client',	'UTA 6.1',	'5FC608ED-EAC7-4085-A639-6891A5ACD2E2',	'Weblogic Domain',	'TAE11'),
('ACA1001',             	'Client',	'UTA 6.1',	'5FF26192-E09E-4401-94C2-1F6047A04820',	'Application Server',	'value5'),
('ACA1001',             	'Client',	'UTA 6.1',	'532046BE-D7F7-4B99-8478-0A35F27AB8FA',	'SSO Integration',	'Y'),
('ACA1001',             	'Client',	'UTA 6.1',	'66BDA18F-1766-4AD0-A635-96F3D10A69F3',	'C10URL',	'value6'),
('ACA1001',             	'Client',	'UTA 6.1',	'9A05EAD4-FD1C-4418-B563-069E6EC9C6C1',	'C10 Database Name',	'C10_24EE0'),
('ACA1001',            		'Client',	'UTA 6.1',	'C31D0D4A-1EDE-4899-81AA-D43D9F4BC48B',	'Port Number',	'7016'),
('ACA1001',             	'Client',	'UTA 6.1',	'D2A6B2E8-4BF4-49BF-A81E-FF16A10ABE05',	'SQL Password',	'************'),
('ACA1001',             	'Client',	'UTA 6.1',	'D908DFFC-AC38-4749-97D8-B608CCFE4D3B',	'Admin Server',	'value7')


Select	
	arNumber
,	customerNumber
,	descrip
,	max([Admin Server])			as	adminServer
,	max([Application Server])	as	apServer
,	max([C10 Database Name])	as	c10dbName
,	max([C10 Server])			as	c10Server
,	max([C10URL])				as	c10URL
,	max([C8DatabaseName])		as	c8dbName
,	max([C8Server])				as	c8Server
,	max([C8URL])				as	c8URL
,	max([Customs])				as	customs
,	max([Environment Setup Date])	as environmentSetupDate
,	max([SSO Integration])			as	ssoIntegration
,	max([Port Number])				as	portNumber
,	max([ReportNetDatabaseName])	as	reportNetDBName
,	max([ReportNetServer])			as	ReportNetSErver
,	max([ReportNetURL])				as	reportNETURL	
,	max([SQL Password])				as	sqlPassword
,	max([SQL Server])				as	sqlServer
,	max([UTA Database Name])			as	UTAdbName
,	max([Version Number])			as	versionNumber
,	max([Web Servers])				as	webServers
,	max([Weblogic Domain])			as	webLogicDomain
from	@myTable
Pivot 
(
	 MAX(FieldValue) for FieldName in ([Admin Server],[Application Server],[C10 Database Name],[C10 Server],[C10URL],[C8DatabaseName],[C8Server],[C8URL],[Customs],[Environment Setup Date],[SSO Integration],[Port Number],[ReportNetDatabaseName],[ReportNetServer],[ReportNetURL],[SQL Password],[SQL Server],[UTA Database Name],[Version Number],[Web Servers],[Weblogic Domain]) 
) pvt

group by arNumber,
customerNumber,
descrip

Open in new window

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
Leo TorresSQL DeveloperAuthor Commented:
Away from job will try to log in 6pm Est and give the code a shot.
Thanks.

Chris you may be right stupid mistake I have been looking at if for so long I totally over looked it. I did not write the into I copied pasted it so I just assume it was right and that's it. Thanks for being that extra set of eyes. doing this at 3:37AM is not a good idea.
Leo TorresSQL DeveloperAuthor Commented:
Same result NULL values where values exists. Dont know if it makes a difference but there are some field values that are NULL.


--sp_dbcmptlevel 'issPortal',90
--,MAX(COALESCE([AdminServer], AdminServer)) AS 'AdminServer'

Select 
arNumber
,CustomerName
,Descrip
,max(AdminServer) AdminServer
,max(ApplicationServer) ApplicationServer
,max(C10DatabaseName) C10DatabaseName
,max(C10Server) C10Server
,MAX(ReportNetURL) ReportNetURL
,max(C8URL) C8URL
,max(C10URL) C10URL
,max(C8DatabaseName) C8DatabaseName
,max(C8Server) C8Server
,max(Customs) Customs
,max(EnvironmentSetupDate) EnvironmentSetupDate
,max(Integration) Integration
,max(PortNumber) PortNumber
,max(ReportNetDatabaseName) ReportNetDatabaseName
,max(ReportNetServer) ReportNetServer
,max(SQLPassword) SQLPassword
,max(SQLServer) SQLServer
,max(SSOIntegration) SSOIntegration
,Max(UTADatabaseName) UTADatabaseName
,max(VersionNumber)VersionNumber
,max(WebServers) WebServers
,max(WeblogicDomain) WeblogicDomain
from 
( 
Select distinct c.arNumber,c.CustomerName, cpa.Descrip,ct.CustomerThirdPartyAppFieldID
,case 
	When ltrim(rtrim(replace(tpa.FieldName,'?',''))) Like '%C10 URL%' Then 'C10URL' 
	ELSE ltrim(rtrim(replace(tpa.FieldName,'?','')))
end FieldName	--c.arNumber,c.CustomerName, cpa.Descrip,ltrim(rtrim(tpa.FieldName)),ct.FieldValue 
--,isNUll(ct.FieldValue,'') FieldValue 
,cast(ct.FieldValue as varchar(max)) FieldValue
from dbo.customerthirdpartyappfield ct (nolock) 
	Join (
			SELECT distinct CustomerThirdPartyAppID
				FROM   dbo.customerthirdpartyappfield (nolock) 
			WHERE  fieldvalue LIKE '%time%' 
			AND fieldvalue NOT LIKE '%wb61%' 
			AND fieldvalue NOT LIKE '%test%') a
		on  ct.CustomerThirdPartyAppID = a.CustomerThirdPartyAppID
	Join dbo.ThirdPartyAppField tpa
		on tpa.ThirdPartyAppFieldID = ct.ThirdPartyAppFieldID
	Join dbo.customerthirdpartyappfield cta (nolock) 
		on ct.CustomerThirdPartyAppID = cta.CustomerThirdPartyAppID
	Left Join customerthirdpartyapp	cpa (nolock)
		on cta.CustomerThirdPartyAppID = cpa.CustomerThirdPartyAppID
	Join Customer c (nolock)
		on c.CustomerID = cpa.CustomerID
--Order by 1		
) src		
Pivot ( MAX(src.FieldValue) for src.FieldName in ([AdminServer],[ApplicationServer],[C10DatabaseName],[C10Server],[C10URL],[C8DatabaseName],[C8Server],[C8URL],[Customs],[EnvironmentSetupDate],[Integration],[PortNumber],[ReportNetDatabaseName],[ReportNetServer],[ReportNetURL],[SQLPassword],[SQLServer],[SSOIntegration],[UTADatabaseName],[VersionNumber],[WebServers],[WeblogicDomain]) ) pvt
Group by 
arNumber
,CustomerName
,Descrip

Order by 1

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try my "SELECT *" suggestion?
Leo TorresSQL DeveloperAuthor Commented:
had the name incorrect thanks
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
Query Syntax

From novice to tech pro — start learning today.