Date:         Thu, 30 Aug 2001 00:45:52 GMT
Reply-To:     Kamal Desai <kamal_desai@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kamal Desai <kamal_desai@YAHOO.COM>
Organization: RoadRunner - Triad
Subject:      Can SQL convert set of columns in to observations?

I want to use SQL to convert set of fields into rows.
I DO NOT WANT TO USE DATASTEP. We may move this data into Other formats and
SQL code is preferred (In future to convert additional records).

My current Dataset is like this.
<Record-1>
-----------
Field:                    Value
----- --------------------
Name:                 XXX Corp. (Text Field)
Ship1Method:     UPS (Text Field)
Ship1Pref:           YES (Yes/No Field)
Ship1Comments: Only if weight > 2 lbs. (Text Field)
Ship2Method:      FEDEX
Ship2Pref:            NO
Ship2Comments:
Ship3Method:      USPS
Ship3Pref:           YES
Ship3Comments:
Bill1Method:        MC CARD
Bill1Pref:              NO
Bill1Comments:
Bill2Method:        VISA CARD
Bill2Pref:              YES
Bill2Comments:   Purchases > $100 only
Bill3Method:       DISC CARD
Bill3Pref:             YES
Bill3Comments:
Bill4Method:       AMEX
Bill4Pref:             NO
Bill4Comments:

--- < END OF RECORD> ---
... More Records ...

i.e. all fields come in set of three -
First one is method
Second one is Preference (YES/NO)
Third one is comments

I want to rearrage the data to following format:

<Record-1>
Name= XXX Corp.
Field_Val = UPS
Field_pref = YES
Field_Comm = Only if weight > 2 lbs.
<End Record-1>

<Record-2>
Name= XXX Corp.
Field_Val = FEDEX
Field_pref = NO
Field_Comm =
<End Record-2>

<Record-3>
Name= XXX Corp.
Field_Val = USPS
Field_pref = YES
Field_Comm =
<End Record-3>

<Record-4>
Name= XXX Corp.
Field_Val = MC CARD
Field_pref = NO
Field_Comm =
<End Record-4>

<Record-5>
Name= XXX Corp.
Field_Val = VISA CARD
Field_pref = YES
Field_Comm = Purchases > $100 only
<End Record-5>

..... and so on .....

Can SQL do this?