Leo Torres
asked on
SQL 2008 Pivot
The inner query has data
but when I pivot everything is null. What Am i doing wrong?
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
ASKER
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.
There is data in the inner query. That's what I had originally but I can figure out why I am getting NULLS.
Can you post some sample data?
FieldValue it's VARCHAR data type?
FieldValue it's VARCHAR data type?
ASKER
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
EE-sample.xlsx
EE-samplePvt.bmp
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?
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
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],[Applicatio nServer],[ C10Databas eName]...
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.
Make sure that the field names you've specified in your PIVOT statement:
Pivot ( MAX(FieldValue) for FieldName in ([AdminServer],[Applicatio
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
Did you try my "SELECT *" suggestion?
ASKER
had the name incorrect thanks
Open in new window
BTW, I have removed the COALESCE([AdminServer], AdminServer), as it does not make sense. Use AdminServer