CODEWITHSUNDEEP
sqlunionsql-server-2014unpivotwith-statement
user6408649
user6408649

Reputation: 1305

UNION several WITH results

I have two similar queryes.

Like folowing:

    with CategoryHistory AS (
    SELECT
    category.GUID as id,
    ----HERE MANY FIELDS----
    category.__$operation AS operation,
    map.tran_begin_time as beginT, 
    map.tran_end_time as endT
    FROM cdc.fn_cdc_get_all_changes_dbo_EXT_BalanceHC_ZapasCategory(sys.fn_cdc_get_min_lsn('dbo_EXT_BalanceHC_ZapasCategory'), sys.fn_cdc_get_max_lsn(), 'all') AS category
    INNER JOIN  [cdc].[lsn_time_mapping] map
        ON category.[__$start_lsn] = map.start_lsn
    )
    SELECT  operation, fields, valueses, beginT, endT FROM CategoryHistory
    unpivot ( [valueses] for fields in
    (

    ----HERE MANY FIELDS----
))p

Can i UNION of WITH results? Problem that i want UNPIVOT some fields from result of query.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Perhaps you simply want something like this:

SELECT ch.operation, ch.beginT, ch.endT, v.field, v.val
FROM CategoryHistory ch cross apply
     (values ('field1', field1), ('field2', field2), . . .
     ) f(field, val);

I find this easier than using unpivot.

Upvotes: 1

Related Questions