I have a table with 65 fields. I need a select statement that will return data from a single record that contains three columns and 21 rows (the two missing fields are keys). I must be able to control the order of the rows.
output needs to look like this
field_x_1 field_x_2 field_x_3
field_A_1 field_A_2 field_A_3
field_Y_1 field_Y_2 field_Y_3
I have tried the following but it says there is an issue with the WITH.
WITH X AS (
WITH T AS (SELECT * FROM Table_Name WHERE FieldZ = '1' AND FieldY = '2')
SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R
SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R
-- I would add 19 more select statements here
SELECT A, B, C
ORDER BY R
I realize I could rewrite it to have my WHERE clause in it 21 times but that seems inefficient. If I only use one CTE I don't know how to guarantee the order without also outputting R.