I am newbie in SQL so apologize if the question does not make sense.
I have a table that looks like (I am on google colab)
CREATE TEMP TABLE test (
name STRING,
subjectId INT,
score INT
);
INSERT INTO test (name, subjectId, score) VALUES ('David', 123, 90);
INSERT INTO test (name, subjectId, score) VALUES ('David', 10, 80);
INSERT INTO test (name, subjectId, score) VALUES ('Ana', 34, 75);
INSERT INTO test (name, subjectId, score) VALUES ('Ryan', 123, 100);
There is a column called subjectId. I would like to apply the PIVOT operator on. I do not know what are the distinct values in this column. So I first create a string to record the distinct value of this column:
DECLARE subjects STRING;
SET subjects = (
SELECT
CONCAT('(', STRING_AGG(DISTINCT CAST(subjectId AS STRING), ', '), ')'),
FROM
test
);
Now I can proceed to apply the PIVOT operation:
EXECUTE IMMEDIATE format("""
SELECT
*
FROM (
SELECT name, subjectId, score FROM test
) AS T
PIVOT
(
max(score) for subjectId in %s
) AS P
""", subjects);
This give me the following table:
Now you can see the columns are named according to the distinct values of the subjectId column. What I would like to do is to rename those columns to something like subject1, subject2, subject3, etc., and I do not care about the order. How can I do that?
