I get error on union all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Question

0.00/5 (No votes)

See more:

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

HTML

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:

SQL

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 9-Feb-20 12:14pm

ahmed_sa

Updated 11-Feb-20 4:07am

v3

Add a Solution

2 solutions

Solution 2

Quote:

SQL

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:

SQL

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);

Posted 11-Feb-20 4:07am

Richard Deeming

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Accepted Answer · 2020-02-10T04:15:00

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:

SQL

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.

Posted 10-Feb-20 4:15am

ZurdoDev

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.