February 9, 2020 at 11:27 pm
problem
I get error All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
I need to display data plus one record display as first row as structure of data
the following data is desired result
bold row i need to added
ItemIdIPNPartnerNameCustomerNameFanMotorRefrigatortemprature
11233SaicoNULLNULLNULLNULL55567
25433MbabyNULL23444NULLNULLNULL
3590444nagiebNULLNULLNULL556666NULL
link for data
http://www.mediafire.com/file/s6qvxpd83z1zssi/datafeatures.sql/file
What I have tried:
create table #ItemFeatures
(
CustomerName nvarchar(200),
CustomerId nvarchar(50)
)
insert into #ItemFeatures
(
CustomerName
)
values
('Avidyne')
Exec(@sql)
update tmp
set tmp.CustomerId = c.CustomerID
from #ItemFeatures tmp inner join pcn.Customers c on c.CustomerName = tmp.CustomerName
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--select distinct Features
(select distinct FeatureName from [CustomerLocations].[FeatureTypes]
) AS B
ORDER BY B.FeatureName
--select @Columns
--pivot table make count for item to every Feature Based on features Name
DECLARE @SQLs as VARCHAR(MAX)
SET @SQLs = 'select ''ItemId'', ''IPN'',''PartnerName'',''CustomerName'',
'' + @Columns + '' union all
SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
(
select F.ItemId ,t.FeatureName,F.FeatureValue,I.IPN,I.PartnerName,FI.CustomerName
from [CustomerLocations].[ItemFeatures] F
Inner Join [CustomerLocations].[Items] I ON F.ItemId=I.ItemId
inner join CustomerLocations.FeatureTypes T on T.FeatureId=F.FeatureId
inner join #ItemFeatures FI on I.CustomerID=FI.CustomerID
) as PivotData
PIVOT
(
max(FeatureValue)
FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
'
EXEC(@SQLs)
drop table #ItemFeatures
February 10, 2020 at 3:34 am
Too hard to just copy & paste your CREATE TABLE and INSERT scripts here?
February 10, 2020 at 4:31 pm
Your "header" row has five columns, but your details rows have eight. Without knowing what your final goal is, it's hard to tell you the best way to resolve this issue.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 22, 2020 at 6:15 am
Hello ahmed_elbarbary.2010
I have tried to find out the problem and i found the solution that you have a syntax error in following statement
SET @sqls = 'select ''ItemId'', ''IPN'',''PartnerName'',''CustomerName'',
'' + @Columns + '' union all
SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
(
select F.ItemId ,t.FeatureName,F.FeatureValue,I.IPN,I.PartnerName,FI.CustomerName
from [CustomerLocations].[ItemFeatures] F
Inner Join [CustomerLocations].[Items] I ON F.ItemId=I.ItemId
inner join CustomerLocations.FeatureTypes T on T.FeatureId=F.FeatureId
inner join #ItemFeatures FI on I.CustomerID=FI.CustomerID
) as PivotData
PIVOT
(
max(FeatureValue)
FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
'
Please replace this statement in the code with following code
SET @sqls =
' SELECT ''ItemId'', ''IPN'',''PartnerName'',''CustomerName'',
' + @Columns + ' union all
SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
(
select F.ItemId ,t.FeatureName,F.FeatureValue,I.IPN,I.PartnerName,FI.CustomerName
from [ItemFeatures] F
inner Join [Items] I ON F.ItemId=I.ItemId
inner join CustomerLocations.FeatureTypes T on T.FeatureId=F.FeatureId
inner join #ItemFeatures FI on I.CustomerID=FI.CustomerID
) as PivotData
PIVOT
(
max(FeatureValue)
FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
'
and try execute your code.
You will not get the error like "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists"
Best of luck.
Please mark the answer true if successfully executed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply