Date:         Wed, 13 Jan 1999 15:49:16 -0500
Reply-To:     "Ward, David" <DWard@DAS-INTERNATIONAL.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Ward, David" <DWard@DAS-INTERNATIONAL.NET>
Subject:      Re: Transpose Data with SQL
Comments: To: Nigel Tufnel <dousk8@HOTMAIL.COM>
Content-Type: text/plain

If your dataset is called "temp", here is one solution.  It is not generic,
but I don't think it is possible to easily mimic the power of proc transpose
in SAS's version of SQL.
 
proc sql;
  select date, joe as value, 'JOE' as person length=3  from temp
 union
  select date, bob as value, 'BOB' as person length=3 from temp
 union
  select date, jan as value, 'JAN' as person length=3 from temp
  order by date, person;
quit;
 
> ----------
> From:         Nigel Tufnel[SMTP:dousk8@HOTMAIL.COM]
> Sent:         Wednesday, January 13, 1999 2:01 PM
> Subject:      Transpose Data with SQL
>
> Is there a way to transpose data with SQL only?
>
> e.g.
>
> Transpose this:
> Date    joe   Bob   Jan
> 1/1/98    1     2     1
> 1/2/98    2     2     2
> 1/3/98    2     3     1
>
> To:
> Date    Person Value
> 1/1/98   Joe       1
> 1/1/98   Bob       2
> 1/1/98   Jan       2
> 1/2/98   Joe       2
> 1/2/98   Bob       2
> 1/2/98   Jan       2
> 1/3/98   Joe       2
> 1/3/98   Bob       3
> 1/3/98   Jan       1
>
> I can do this easily in a data step or Proc SQL, but I need a SQL
> solution.
>
> Thanks
> Nigel
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>