CODEWITHSUNDEEP
sqlsql-servert-sql
Jack Reilly
Jack Reilly

Reputation: 168

How to populate a column's value based on two other column's values

I have a table like this:

|  DEL_ID   |   CP_ID   | ID  | QUANTITY   | FP_ID  |  RESULT  |

I need to populate the result column by using the Quantity and FP_ID columns by concatenating the FP_ID value to itself, and do so as many times as the value of QUANTITY.

So RESULT = FP_ID concatenated to itself QUANTITY times.

If QUANTITY is 3 and FP_ID is 23 then the result should be 232323

I need to do an insert that inserts this result for each row based on this logic.

How do I do this in SQL?

Upvotes: 0

Views: 789

Answers (2)

koushik veldanda
koushik veldanda

Reputation: 1107

It should be computed column....

I mean I should be declared as it is based on other two columns

if you want to add after creation of table 

ALTER TABLE tblResults
ADD Final_Result as replicate(FP_ID ,Quantity)

else

while creation 

    Create table tblResults
(.......ur columns..... , Final_Result as replicate(FP_ID ,Quantity))

you no need to give data for this column, It will automatically loaded when the data is loaded into table "tblResults"

NOTE: If any value is null then the Final_Result value will also be NULL

Upvotes: 4

Bala
Bala

Reputation: 704

I have edited my answer as below:

Please execute the below query to create a Function:

CREATE FUNCTION [dbo].[ConcatenateString]
(
    @FP_ID INT,
    @QUANTITY INT
)
RETURNS
    NVARCHAR(MAX) 

AS
BEGIN
    DECLARE @ConcatenatedString NVARCHAR(MAX), @ConvertedFP_ID NVARCHAR(100) 
    SET @ConcatenatedString =''
    SET @ConvertedFP_ID = CONVERT(varchar(100),@FP_ID)

    WHILE @QUANTITY >= 1
        BEGIN
            SELECT @ConcatenatedString  = @ConcatenatedString+@ConvertedFP_ID
            SET @QUANTITY = @QUANTITY - 1
        END

    RETURN @ConcatenatedString
END
GO

And you can call the Function in the INSERT script:

INSERT INTO tblResults(DEL_ID,CP_ID,ID,QUANTITY,FP_ID,RESULT)
VALUES(1,2,3,4,5,(SELECT dbo.ConcatenateString(4,5) AS ConcatnatedValue))

Upvotes: 0

Related Questions