All queries combined using a UNION, INTERSECT or EXCEPT oper

problem

error

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

data on this link

http://www.mediafire.com/file/s6qvxpd83z1zssi/datafeatures.sql/file

I need to display data plus one record display as first row as structure of data

the following data is desired result

red row i need to added

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.