I have a table like below.
<code>-- Sample data setup
CREATE OR REPLACE TEMP TABLE your_table
(
ID VARCHAR(50),
EntityName VARCHAR(50),
Value VARCHAR(50)
);
INSERT INTO your_table (ID, EntityName, Value)
VALUES
('IL15342506861', 'AAP_DENOM', '02107'),
('IL15342506861', 'AAP_DENOM_PYCM', '098'),
('IL15342506861', 'AAP_DENOM_PYEM', '2154'),
('IL15342506861', 'AAP_NUM', '3241'),
('IL15342506861', 'AAP_NUM_PYEM', '215'),
('IL15342506861', 'AGE', '23'),
('IL15342506213', 'AAP_DENOM_PYEM', '134'),
('IL15342506213', 'AGE', '12');
</code>
<code>-- Sample data setup
CREATE OR REPLACE TEMP TABLE your_table
(
ID VARCHAR(50),
EntityName VARCHAR(50),
Value VARCHAR(50)
);
INSERT INTO your_table (ID, EntityName, Value)
VALUES
('IL15342506861', 'AAP_DENOM', '02107'),
('IL15342506861', 'AAP_DENOM_PYCM', '098'),
('IL15342506861', 'AAP_DENOM_PYEM', '2154'),
('IL15342506861', 'AAP_NUM', '3241'),
('IL15342506861', 'AAP_NUM_PYEM', '215'),
('IL15342506861', 'AGE', '23'),
('IL15342506213', 'AAP_DENOM_PYEM', '134'),
('IL15342506213', 'AGE', '12');
</code>
-- Sample data setup
CREATE OR REPLACE TEMP TABLE your_table
(
ID VARCHAR(50),
EntityName VARCHAR(50),
Value VARCHAR(50)
);
INSERT INTO your_table (ID, EntityName, Value)
VALUES
('IL15342506861', 'AAP_DENOM', '02107'),
('IL15342506861', 'AAP_DENOM_PYCM', '098'),
('IL15342506861', 'AAP_DENOM_PYEM', '2154'),
('IL15342506861', 'AAP_NUM', '3241'),
('IL15342506861', 'AAP_NUM_PYEM', '215'),
('IL15342506861', 'AGE', '23'),
('IL15342506213', 'AAP_DENOM_PYEM', '134'),
('IL15342506213', 'AGE', '12');
I want result like below.
I have to create view that works without specifying EntityName
in the query because EntityName
may vary, which means I don’t want to add manual EntityName
while writing query.
<code>CREATE OR REPLACE VIEW pivoted_view AS
SELECT
ID,
MAX(CASE WHEN Name = 'AAP_DENOM' THEN Value END) AS AAP_DENOM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYCM' THEN Value END) AS AAP_DENOM_PYCM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYEM' THEN Value END) AS AAP_DENOM_PYEM,
MAX(CASE WHEN Name = 'AAP_NUM' THEN Value END) AS AAP_NUM,
MAX(CASE WHEN Name = 'AAP_NUM_PYEM' THEN Value END) AS AAP_NUM_PYEM,
MAX(CASE WHEN Name = 'AGE' THEN Value END) AS AGE
FROM your_table
GROUP BY ID
</code>
<code>CREATE OR REPLACE VIEW pivoted_view AS
SELECT
ID,
MAX(CASE WHEN Name = 'AAP_DENOM' THEN Value END) AS AAP_DENOM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYCM' THEN Value END) AS AAP_DENOM_PYCM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYEM' THEN Value END) AS AAP_DENOM_PYEM,
MAX(CASE WHEN Name = 'AAP_NUM' THEN Value END) AS AAP_NUM,
MAX(CASE WHEN Name = 'AAP_NUM_PYEM' THEN Value END) AS AAP_NUM_PYEM,
MAX(CASE WHEN Name = 'AGE' THEN Value END) AS AGE
FROM your_table
GROUP BY ID
</code>
CREATE OR REPLACE VIEW pivoted_view AS
SELECT
ID,
MAX(CASE WHEN Name = 'AAP_DENOM' THEN Value END) AS AAP_DENOM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYCM' THEN Value END) AS AAP_DENOM_PYCM,
MAX(CASE WHEN Name = 'AAP_DENOM_PYEM' THEN Value END) AS AAP_DENOM_PYEM,
MAX(CASE WHEN Name = 'AAP_NUM' THEN Value END) AS AAP_NUM,
MAX(CASE WHEN Name = 'AAP_NUM_PYEM' THEN Value END) AS AAP_NUM_PYEM,
MAX(CASE WHEN Name = 'AGE' THEN Value END) AS AGE
FROM your_table
GROUP BY ID
I heard that we can create JavaScript function that will create function and we can use it while creating view. but I don’t have that much knowledge. Please help me with requirement.