Alter View -Two New Feilds Adding

Transact-SQL
1

Hello All,

We have a view that is created on the DB to bring in all the records for a request into a single column . Below is the View

CREATE View [dbo].[CustPolicies] as
View [dbo].[Policies] as
WITH
view1 AS
(
SELECT DISTINCT Reqno FROM CustPolicies
)
SELECT

a
.reqno,
STUFF(b.Pol,1,1,'') as Pol
FROM
view1 a
OUTER APPLY

(

        SELECT ',' + Pol

FROM CustPolicies p

WHERE p.[Reqno] = a.[Reqno]
FOR XML PATH('')

) b(Pol)

GO

We would like to add two new fields to this view .

  1. the first field should have 6 preceding zeroes and each policy separated by a coma
    1.Example 1 000000123456
    2.Example 2 000000123456,0000009876543,000000675432

  2. Another field to have the same 6 preceding 6 zeroes and concatenated between single quotes
    1.Example 1 – ‘000000123456’
    2.Example 2 ‘000000123456’,’0000009876543’,’000000675432’

Need help to add the new feilds

Thanks

2

without DDL and sample data it will be difficult to show you anything. but you could try something like this:

declare @i int = 123
select REPLICATE('0',6) + cast(@i as varchar(10))

3

hi

i tried to create the view

syntax is all WRONG