Can this be done with a cross tab query?

[mmohon]

Ok, first off, I know the data is organized poorly....but I didnt
organize it. I'm just trying to cope

heres an example of what i got


Key____SIP1_____SIP2
-----------------------------------
123____"D"_______"E"
124____"E"_______"D"

I need to re arrange like this:

Key____Type_____Val
----------------------------------
123____SIP1_____"D"
123____SIP2_____"E"
124____SIP1_____"E"
124____SIP2_____"D"

I'm guessing that can be done with a crosstab query but, I'm not quite
sure how to set what to what as far as row headings and stuff. Does
anyone else have any ideas?

 

[Guest]

Actually a crosstab won't work as it's already set up like the results of a
crosstab - or rather a spreadsheet. Not your fault.

Union queries will work. The "SIP1" AS Type will insert the value SIP1
into a Type column. You just need to create another UNION ALL and matching
SELECT statement for each "SIP" column in the table.

SELECT Key, "SIP1" AS Type, SIP1 AS Val
FROM TableName
UNION ALL
SELECT Key, "SIP2" AS Type, SIP2 AS Val
FROM TableName;

 

[Douglas J. Steele]

A Union query should be sufficient:

SELECT Key, "SIP1" AS Type, SIP1 AS Val
FROM MyTable
UNION
SELECT Key, "SIP2" AS Type, SIP2 AS Val
FROM MyTable