By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET NOCOUNT ON
drop table if exists #tmp;
DECLARE @Item TABLE(
ItemId INT,
Name VARCHAR(25)
);
INSERT INTO @Item( ItemId, Name)
VALUES(1,'Item 1'),
(2,'Item 2');
DECLARE @Property TABLE(
PropertyId INT,
Name VARCHAR(10)
);
INSERT INTO @Property(PropertyId,Name)
VALUES( 1,'Property 1'),
(2,'Property 2'),
(3,'Property 3');
DECLARE @ItemProperty TABLE(
ItemId INT,
PropertyId INT
);
INSERT INTO @ItemProperty(ItemId,PropertyId)
VALUES(1,1),(1, 2),( 2, 2),(2,3);
DECLARE @SQL AS VARCHAR(MAX);
DECLARE @WithCols VARCHAR(MAX);
| (No column name) |
|---|
| [{"itemID":1,"Name":"Item 1","Property 1":"1","Property 2":"1","Property 3":"0"}] |
| itemID | Name | Property 1 | Property 2 | Property 3 |
|---|---|---|---|---|
| 1 | Item 1 | 1 | 1 | 0 |