Hi,

I have query with union .

DECLARE @U2TableName AS VARCHAR(200)='CIS.CUST.MAST'
 
INSERT INTO BIBKUL8.eMarine_Demo.[dbo].UpdateU2
select  @U2TableName AS TableName,Name As Field,'1010001'+ CustomerCode AS Z_ID ,'1' AS Pos from CIS  WHERE  ExportedToU2 IS NULL
UNION 
select  @U2TableName AS TableName,Initial As Field,'1010001'+ CustomerCode AS Z_ID ,'2' AS Pos from CIS  WHERE  ExportedToU2 IS NULL
UNION 
select  @U2TableName AS TableName,ModuleType As Field,'1010001'+ CustomerCode AS Z_ID ,'3' AS Pos from CIS  WHERE  ExportedToU2 IS NULL

How to write above query without UNION is nothing but in a SINGLE STATEMENT?

Please help me to solve.

Thank you in advance.

• Edited by Narsa 4 hours 11 minutes ago

Try

DECLARE @U2TableName AS VARCHAR(200)='CIS.CUST.MAST'
 
INSERT INTO BIBKUL8.eMarine_Demo.[dbo].UpdateU2
select  @U2TableName AS TableName,Name As Field,'1010001'+ CustomerCode AS Z_ID , cast(N.Number as char(1))  AS Pos from CIS  
CROSS APPLY (select Number from Numbers where Number between 1 and 3) N

WHERE  ExportedToU2 IS NULL

If you don't have Numbers table in your database, here is an alternative (SQL Server 2008 and up):

DECLARE @U2TableName AS VARCHAR(200)='CIS.CUST.MAST'
 
INSERT INTO BIBKUL8.eMarine_Demo.[dbo].UpdateU2
select  @U2TableName AS TableName,Name As Field,'1010001'+ CustomerCode AS Z_ID , cast(N.Number as char(1))  AS Pos from CIS  
CROSS APPLY (values (1),(2),(3)) N (Number)

WHERE  ExportedToU2 IS NULL

I don't use this syntax too often, so I did a quick look up and found good article

http://sqlstudies.com/2013/04/01/unpivot-a-table-using-cross-apply/

Hi,

Thank you for quick reply.

But  "Field "  Column having same value by using your query

Current query result :

TableName	Field	Z_ID	pos
CIS.CUST.MAST	testing1	1010001C1500001	1
CIS.CUST.MAST	testing1	1010001C1500001	2
CIS.CUST.MAST	testing1	1010001C1500001	3

"Field" Column values would be taken from "CIS" table, i.e "Field" Column declaration will be changed based on "Pos" column.

Ex  : If pos has '1' then Field column "Name" 

If pos has'2' then  Field column "Initial" 

If pos has '3' then Field column "ModuleType" ....... If pos has '90' then Field column "Status"

Note : Total Pos : 1 to 90 

Desired result  :

TableName	Field	Z_ID	pos
CIS.CUST.MAST	testing1	1010001C1500001	1
CIS.CUST.MAST	TPW	1010001C1500001	2
CIS.CUST.MAST	DIR	1010001C1500001	3

please help.

Thank you

I see, I didn't notice. Anyway, my second solution is the way to go then:

DECLARE @U2TableName AS VARCHAR(200)='CIS.CUST.MAST'
 
INSERT INTO BIBKUL8.eMarine_Demo.[dbo].UpdateU2
select  @U2TableName AS TableName, N.Field,'1010001'+ CustomerCode AS Z_ID , cast(N.Number as char(1))  AS Pos from CIS  
CROSS APPLY (values (1, Name),(2, Initial),(3, ModuleType)) N (Number, Field)

WHERE  ExportedToU2 IS NULL

Check the blog I pointed you to, it has nice explanation.

Thank you very much... :)

Following the article provided by Naomi N, I think the right answer is:

DECLARE @U2TableName AS VARCHAR(200)='CIS.CUST.MAST'
 
INSERT INTO BIBKUL8.eMarine_Demo.[dbo].UpdateU2
select  @U2TableName AS TableName,Name As Field,'1010001'+ CustomerCode AS Z_ID ,N.Field, cast(N.Number as char(1))  AS Pos from CIS  
CROSS APPLY (
              values (Name, 1)
                     ,(Initial, 2)
                     ,(ModuleType, 3)
            ) N (Field, Number)

WHERE  ExportedToU2 IS NULL