I hope the question can be understood, I'll try to clarify further.
So I have the following tables:
lines:
id type_id cost
types:
id name
I would like to create a select statement that creates a column per name in the types table. For example if there were types "wood", "metal", "steel", it would look like this:
id wood metal steel
And under each column would be the total cost of each per line.
I have no clue how I can transform column values into select column headers and associate the column values with a sum value.
What you're describing is often called a pivot table.
You can't do this in a single query in SQL. All columns must be fixed in the query before the query begins reading any data. So there's no way the values it reads can dynamically add more columns.
So you have to know the columns before you write your query. You can certainly read the values from your table:
SELECT id, name FROM types;
Collect the results into an array in your Python client code (I'm assuming you use Python based on your username on Stack Overflow, but you can do this in any client code regardless of language), then use it to format a query string for the second query:
SELECT id,
SUM(CASE type_id WHEN 1 THEN cost END) AS `wood`,
SUM(CASE type_id WHEN 2 THEN cost END) AS `metal`,
SUM(CASE type_id WHEN 3 THEN cost END) AS `steel`
FROM lines
GROUP BY id;
An alternative that is easier than a pivot table, is to run a grouping query:
SELECT lines.id, types.name AS type_name, SUM(cost) AS total_cost
FROM lines
JOIN types ON lines.type_id = types.id
GROUP BY lines.id, types.name;
This returns multiple rows, and it's up to you to collect these in your client code and present them in a tabular format as you want.
One way or the other, you'll end up writing some code. Either code to format the pivot-table query, or else code to display the results of the alternative grouping query.