view key value rows as multi column single row

Question

You have parameters x,y,z stored as key values.

You want to execute an expression z=x+y on those parameters. Expression is stored in another table.

You want to generate an SQL query as simply as possible from the expression.

How can you view those parameter values as a single row with columns (x,y,z) to enable execution of the expression ?

SELECT *
 INTO #key_values
FROM
(
SELECT 'x' AS mykey, 2 AS myvalue
UNION ALL
SELECT 'y',          5
UNION ALL
SELECT 'z',          0
) a;

"Expression is provided in the form of input data." What does this mean? — Gordon Linoff, Nov 12 '19 at 13:33
changed to "Expression is stored in another table". I want to generate a query from the expression. It is actually going to be dynamic SQL with a query string generated with the expression as input. — Ludovic Aubert, Nov 12 '19 at 13:39

score 1 · Accepted Answer · edited Nov 12 '19 at 14:25

1

This screams for a PIVOT operator:

;WITH Inputs AS
(
    SELECT 'x' AS mykey, 2 AS myvalue
    UNION ALL
    SELECT 'y',          5
    UNION ALL
    SELECT 'z',          0
)
SELECT
    U.x,
    U.y,
    U.z,
    Result = U.x + U.y
FROM
    Inputs AS I
    PIVOT (
        MAX(I.myvalue) FOR I.mykey IN (x, y, z)
    ) AS U

Results:

x   y   z   Result
2   5   0   7

You can build any expression you want with the pivoted columns in the SELECT.

If you want to update the z record, you will have to join back to the underlying table since after applying the PIVOT you lose access to original table.

IF OBJECT_ID('tempdb..#Input') IS NOT NULL
    DROP TABLE #Input

CREATE TABLE #Input (
    mykey VARCHAR(10),
    myvalue INT)

INSERT INTO #Input (
    mykey,
    myvalue)
VALUES
    ('x', 2),
    ('y', 5),
    ('z', 0)

UPDATE I SET
    myvalue = R.Result
FROM
    #Input AS I
    CROSS APPLY (
        SELECT
            Result = x + y
        FROM
            #Input AS I
            PIVOT (MAX(I.myvalue) FOR I.mykey IN (x, y, z)) AS U
        ) AS R
WHERE
    I.mykey = 'z'

edited Nov 12 '19 at 14:25

Ludovic Aubert

9,534
4
16
28

answered Nov 12 '19 at 13:35

EzLo

13,780
10
33
38

Indeed relevant. Wondering how I could update z painlessly. – Ludovic Aubert Nov 12 '19 at 14:08
Replaced Result=U.x+U.y by Result=x+y. – Ludovic Aubert Nov 12 '19 at 14:27
Can the PIVOT constraint "For I.mykey IN (x,y,z)" be removed ? Could we tell it to create all possible columns ? – Ludovic Aubert Nov 12 '19 at 14:43
1

@LudovicAubert you will have to do a dynamic pivot then (with dynamic SQL). https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – EzLo Nov 12 '19 at 14:51
For a complete example of PIVOT/UNPIVOT https://www.sqlservercentral.com/blogs/pivot-and-unpivot-table-in-sql-server – Ludovic Aubert Nov 15 '19 at 14:22
PIVOT/UNPIVOT can be used to achieve the transformation in both ways. – Ludovic Aubert Nov 15 '19 at 14:25

score 0 · Answer 2 · answered Nov 12 '19 at 13:28

Turn the 3 rows into a single 3 column row using a common table expression and update it to run the expression. So the proposed solution is an updatable cte.

WITH myvalues(x,y,z) AS (
    SELECT x.myvalue, y.myvalue, z.myvalue 
    FROM #key_values AS x
    JOIN #key_values AS y ON y.mykey='y' AND x.mykey='x'
    JOIN #key_values AS z ON z.mykey='z'
)
UPDATE myvalues SET z=x+y;

SELECT myvalue FROM #key_values WHERE mykey='z';

view key value rows as multi column single row

2 Answers2