Something to reverse Crosstab.

  • Thread starter Thread starter pareshgoyal
  • Start date Start date
P

pareshgoyal

Hi,

I have following data in Excel.

Code GPH SSD MVX ... CS150 (150 cols appx. with no fix col. names)
B103 0 1 0 ...... 1 ( If yes then 1 otherwise 0)
B336 1 0 1 ...... 0

....
....
and so on....for around 400 rows.


I'll port this data to Access / SQL server 2000.

Now I want to get those data as follows -

Code CSCode
B103 SSD
B103 CS150
B336 GPH
B336 MVX
2 rows for Code B103 & 2 Rows B336....if more CSCodes are set as Yes
then....those many rows should appear in the output for each Code.

Can anyone suggest any query.

Regards,
Paresh....
 
Use a UNION - but you won't be able to build a 150 SELECT Union in Access.
You can do them a few at a time and then use the UNION in an Append query:

SELECT Code, "GPH" As CSCode
FROM MyTable
WHERE GPH = 1
UNION
SELECT Code, "SSD" As CSCode
FROM MyTable
WHERE SSD = 1
UNION ...

I think you get the idea.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top