CODEWITHSUNDEEP
sql-servert-sqlsql-update
DaveP
DaveP

Reputation: 109

SQL Combine values in a vertical table using a stored procedure

(I am trying to do this in a SQL Server Stored Procedure)

I have a temporary table in a vertical structure and I need to calculate a value from fields that are on different lines

        DECLARE @tempTable TABLE(

        nodeId INT IDENTITY(1,1),
        parentNodeId INT,
        fieldName NVARCHAR(255),
        fieldValue decimal(7,2)
    );

Here's some sample data:

        INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(0,'deliveryLines',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitPrice',12)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'taxRate',0.5)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitTax',0)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitPrice',25)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'taxRate',0.1)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitTax',0)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitPrice',333)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'taxRate',0.17)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitTax',0)

so the table looks like this:

nodeId parentNodeId fieldName       fieldValue
1      0            deliveryLines   NULL
2      1            deliveryLine    NULL
3      2            unitPrice       12.00
4      2            taxRate         0.50
5      2            unitTax         0.00
6      1            deliveryLine    NULL
7      6            unitPrice       25.00
8      6            taxRate         0.10
9      6            unitTax         0.00
10     1            deliveryLine    NULL
11     10           unitPrice       333.00
12     10           taxRate         0.17
13     10           unitTax         0.00

and the unitTax should be unitPrice * taxRate (from the previous 2 lines with the same parentNodeId).

so for parentNodeId = 2 (for example) the data should look like this:

3      2            unitPrice       12.00
4      2            taxRate         0.50
5      2            unitTax         6.00

I need to set the unitTax value using an UPDATE but I can't figure out how to do it. The data has been generated from an xml document and I don't have any control over the structure. Can anyone help me out?

Upvotes: 1

Views: 72

Answers (1)

Sanpas
Sanpas

Reputation: 1180

Hi i think thoses query can be respond : 

CREATE TABLE #tempTable(
        nodeId INT IDENTITY(1,1),
        parentNodeId INT,
        fieldName NVARCHAR(255),
        fieldValue decimal(7,2)
    );


    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(0,'deliveryLines',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitPrice',12)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'taxRate',0.5)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitTax',0)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitPrice',25)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'taxRate',0.1)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitTax',0)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitPrice',333)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'taxRate',0.17)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitTax',0)


    SELECT * FROM #tempTable;
    SELECT parentNodeId, 
                    MAX(CASE WHEN fieldName = 'unitPrice' THEN fieldValue ELSE NULL END) AS 'unitPrice', 
                    MAX(CASE WHEN fieldName = 'taxRate' THEN fieldValue ELSE NULL END) AS 'taxRate'
         FROM #tempTable  GROUP BY parentNodeId;


    UPDATE T SET fieldValue = TT.unitPrice * TT.taxRate
    FROM #tempTable T 
        JOIN (SELECT parentNodeId, 
                    MAX(CASE WHEN fieldName = 'unitPrice' THEN fieldValue ELSE NULL END) AS 'unitPrice', 
                    MAX(CASE WHEN fieldName = 'taxRate' THEN fieldValue ELSE NULL END) AS 'taxRate'
         FROM #tempTable  GROUP BY parentNodeId) TT ON T.parentNodeId = TT.parentNodeId AND T.fieldName = 'unitTax' 



    SELECT * FROM #tempTable;
    DROP TABLE #tempTable;

I know other alternative i think PIVOT query on update can be work too.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

Upvotes: 1

Related Questions