Skip to Main Content

CTE Issue

3234063May 17 2016 — edited May 22 2016

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

FROM T

UNION ALL

SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

FROM T

-- I would add 19 more select statements here

)

SELECT A, B, C

FROM X

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.

Thanks,

Scott

Comments
Post Details
Added on May 17 2016
16 comments
2,398 views