Combine columns in SQL-CodePudding

I have a table with these columns:

module varchar
cycle Date
autoNum int

SELECT TOP 1000 [id]
      ,[module]
      ,FORMAT([cycle],'yyyyMM') as cycle
      ,RIGHT('000000' CAST([autoNum] as varchar(5)),5) as autoNum
     
  FROM [Inventory].[dbo].[tbl_invoiceGIN]

id	module	cycle	autoNum
1	IN	2301	00001
2	OUT	2301	00001

I want to combine two of the columns to make a new column like this:

id	module	cycle	autoNum	DocNo
1	IN	2301	00001	IN2301-00001
2	OUT	2301	00001	OUT2301-00001

How can I do it?

CodePudding user response：

Repeat the expressions:

SELECT TOP 1000 [id]
    ,[module]
    ,FORMAT([cycle],'yyyyMM') as cycle
    ,RIGHT('000000' CAST([autoNum] as varchar(5)),5) as autoNum
    ,FORMAT([cycle],'yyyyMM')   '-'    RIGHT('000000' CAST([autoNum] as varchar(5)),5) As DocNo    
FROM [Inventory].[dbo].[tbl_invoiceGIN]

Or use nested SELECT statements:

SELECT TOP 1000 id, [module], cycle, autoNum, cycle '-' autoNum As DocNo
FROM (
    SELECT [id]
        ,[module]
        ,FORMAT([cycle],'yyyyMM') as cycle
        ,RIGHT('000000' CAST([autoNum] as varchar(5)),5) as autoNum  
    FROM [Inventory].[dbo].[tbl_invoiceGIN]
) i