About the same SQL Server query get different result in two PCs

Question

Hello guys , there is a problem when I run my SQL query
When I run the follow query in my PC ,

USE SSMES
select coreData.,TTData. from(
SELECT * FROM
(
SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s
WHERE s.c_ProductSN = '1118310XELT20/LYKH/201108A0339' and s.c_Check = '1') as k
PIVOT(MAX(c_MatrlValue) FOR c_MatrlName IN(OP21中间壳体组件,OP22中间壳体组件,OP22背板,OP22压轮条码,OP22涡轮轴,OP50止推轴承条码)) as kk)
as coreData,
(
SELECT * FROM
(
SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s
WHERE s.c_ProductSN = '1118200XELT20/BADJP/201113A773' and s.c_Check = '1') as k
PIVOT(MAX(c_MatrlValue) FOR c_MatrlName IN(OP102电控执行器条码,OP130增压器总成SN,OP140蜗壳条码,OP140增压器总成SN,OP140测试报告路径,OP151涡壳条码,OP152增压器总成SN,OP152蜗壳条码,OP153增压器总成SN,OP153蜗壳条码,OP90压壳条码,OP90中间体条码,OP90蜗壳条码)) AS T22 )
as TTData****

I got the follow result (this is the expected result ,only one row )

but when I run it in my Server , the same SQL query get the follow result (multi rows but not the expected result )

I have tested the SQL query in different PCs & different version of the SQL Server . Only the wrong result in my Server PC , the SQL PC runs the Server 2019 OS . Does the OS have an impact on the Sql Server ? Or some reasons ?

Thanks

Answer 1

Hi @wang xiaodong ,

Welcome to Microsoft Q&A!

In order to find out the possible cause, please provide some details about below firstly:

1. Did all these different PCs connect to the same database or have the completely same database with same tables and data?
2. What are other PCs's OS?
3. Did they have the same environment? (Dev, Test or Prod).

Please also compare the results of below two queries on different PCs:

SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s  
WHERE s.c_ProductSN = '1118310XELT20/LYKH/201108A0339' and s.c_Check = '1'  
  
SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s  
WHERE s.c_ProductSN = '1118200XELT20/BADJP/201113A773' and s.c_Check = '1'  

Or you could post CREATE TABLE statements for your tables together with INSERT statements with sample data, and the expected result of the sample. This permits us to copy and paste into a query window to develop a tested query.

In addition, you could modify the query as below and execute it on different PCs and check whether they could be the same if no other impact affects.

USE SSMES  
  
select coreData.*,TTData.*  
 from(  
SELECT c_ProductSN,c_Check  
,max(OP21中间壳体组件) OP21中间壳体组件  
,max(OP22中间壳体组件) OP22中间壳体组件   
...  
,max(OP50止推轴承条码) OP50止推轴承条码  
FROM  
(  
SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s  
WHERE s.c_ProductSN = '1118310XELT20/LYKH/201108A0339' and s.c_Check = '1') as k  
PIVOT(MAX(c_MatrlValue) FOR c_MatrlName IN(OP21中间壳体组件,OP22中间壳体组件,OP22背板,OP22压轮条码,OP22涡轮轴,OP50止推轴承条码)) as kk  
group by c_ProductSN,c_Check)  
as coreData,  
(  
SELECT c_ProductSN,c_Check  
,max(OP102电控执行器条码) OP102电控执行器条码  
,max(OP130增压器总成SN) OP130增压器总成SN  
...   
,max(OP90蜗壳条码) OP90蜗壳条码  
 FROM  
(  
SELECT s.c_ProductSN, s.c_MatrlName, s.c_MatrlValue, s.c_Check FROM[Tb_MatrlLog] s  
WHERE s.c_ProductSN = '1118200XELT20/BADJP/201113A773' and s.c_Check = '1') as k  
PIVOT(MAX(c_MatrlValue) FOR c_MatrlName IN(OP102电控执行器条码,OP130增压器总成SN,OP140蜗壳条码,OP140增压器总成SN,OP140测试报告路径,OP151涡壳条码,OP152增压器总成SN,OP152蜗壳条码,OP153增压器总成SN,OP153蜗壳条码,OP90压壳条码,OP90中间体条码,OP90蜗壳条码)) AS T22   
group by c_ProductSN,c_Check)  
as TTData  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I am wondering if the collation setting for the server or the database between your SQL server and others are different, You can check the server collation using

SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

or the database collation using:

SELECT name, collation_name FROM sys.databases;