Solved

SQL 2008 Pivot

Posted on 2014-10-05
11
234 Views
Last Modified: 2014-10-07
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

0
Comment
Question by:Leo Torres
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40362933
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
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40363021
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.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40363179
Can you post some sample data?
FieldValue it's VARCHAR data type?
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 8

Author Comment

by:Leo Torres
ID: 40363400
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
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40363460
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

0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40363477
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.
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 40363498
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

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40363629
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.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 40364968
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

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40365333
Did you try my "SELECT *" suggestion?
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40367574
had the name incorrect thanks
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question