FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Product (
  ProductID INT IDENTITY(1, 1),
  Preferred_Data_Point INT,
  Data_Point_1 VARCHAR(10),
  Data_Point_2 VARCHAR(10),
  Data_Point_3 VARCHAR(10)
);

INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3) VALUES ('Hello', 'World', '*wave*');

-- Dynamic Selection Criteria (Define Column Name) (Useful for views)
SELECT 
	[Product].ProductID,
    JSON_VALUE(ProductJSON.JSON, CONCAT('$.Data_Point_', Product.ProductID)) AS [value]
FROM Product [Product] WITH (NOLOCK)
CROSS APPLY -- 1to1 Cross Apply Row w/ JSON equivalent
(
    SELECT (SELECT *
    FROM Product [jsonProduct] WITH (NOLOCK)
  	WHERE jsonProduct.ProductID = Product.ProductID
  	FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) [JSON]
) [ProductJSON];

-- More Obvious
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 2);
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 3);
INSERT INTO Product(Data_Point_1, Data_Point_2, Data_Point_3, Preferred_Data_Point) VALUES ('Hello', 'World', '*wave*', 1);

-- Select based on rows other column values (same as before, but different)
SELECT 
	[Product].ProductID,
    JSON_VALUE(ProductJSON.JSON, CONCAT('$.Data_Point_', Product.Preferred_Data_Point)) AS [value]
FROM Product [Product] WITH (NOLOCK)
CROSS APPLY -- 1to1 Cross Apply Row w/ JSON equivalent
(
    SELECT (SELECT *
    FROM Product [jsonProduct] WITH (NOLOCK)
  	WHERE jsonProduct.ProductID = Product.ProductID
  	FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) [JSON]
) [ProductJSON];

ProductID	value
1	Hello
2	World
3	wave

ProductID	value
1	NULL
2	NULL
3	NULL
4	World
5	wave
6	Hello

(1 rows affected)

(1 rows affected)

(1 rows affected)

(3 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(6 rows affected)