Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
problem

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

I need to add row structure of table as first row so that actually I need to do that as following


i will 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

ItemId	IPN	PartnerName	CustomerName	Fan	Motor	Refrigator	temprature
ItemId	IPN	PartnerName	CustomerName	Fan	Motor	Refrigator	temprature
1	1233	Saico	NULL	NULL	NULL	NULL	55567
2	5433	Mbaby	NULL	23444	NULL	NULL	NULL
3	590444	nagieb	NULL	NULL	NULL	556666	NULL

data tables
datafeatures[^]

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)
Posted
Updated 11-Feb-20 5:07am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
SET @SQLs = 'select  ''ItemId'', ''IPN'',''PartnerName'',''CustomerName'',
       '' + @Columns + '' union all
SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
The syntax highlighting here should give you a clue.

Your first row doesn't contain the same number of columns as your pivot data. It always contains five values:
  • "ItemId"
  • "IPN"
  • "PartnerName"
  • "CustomerName"
  • " + @Columns + "

That last one isn't a list of feature names; it's the literal string " + @Columns + ".

This is the sort of thing that's best handled in the UI. But if you really want to stick with SQL, you'll need a separate variable to hold the values for the heading row:
DECLARE @Columns As varchar(max), @ColumnHeadings As varchar(max);

SELECT 
    @Columns = IsNull(@Columns + ', ','') + QUOTENAME(FeatureName),
    @ColumnHeadings = IsNull(@ColumnHeadings + ', ', '') + '''' + Replace(FeatureName, '''', '''''') + ''''
FROM
    (SELECT DISTINCT FeatureName from [CustomerLocations].[FeatureTypes]) As B
ORDER BY 
    B.FeatureName
;


DECLARE @SQLs As varchar(max);

SET @SQLs = 'SELECT ''ItemId'', ''IPN'', ''PartnerName'', ''CustomerName'', ' + @ColumnHeadings + '
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);
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

The error means that your select items do not have the same number of columns. You're missing some column or have too many in another.

For example, this sql will give that error:
SELECT field1
FROM table1
UNION ALL
SELECT field1, field2
FROM table2


Add PRINT @sqls statements or debug it and step through so you can see what is actually happening. It looks like your @columns variable may end with a , in it.
   
Comments
ahmed_sa 10-Feb-20 17:54pm
   
my problem in @Columns variables before union all not show dynamic values but after union all it display dynamic values so How to solve this problem
ZurdoDev 10-Feb-20 19:16pm
   
I do not understand what you are saying. Regardless, you need to debug it. We can’t do that for you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100