Date:         Thu, 24 Jan 2008 13:27:10 -0500
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Organization: Internet News Service
Subject:      Re: Select by Looping through rows
Comments: To: sas-l@uga.edu

maryann wrote:
> I am trying to figure out how to do looping in SQL.
> I have table like below:
>
> ID System Material
> 1 Floor Wood
> 1 Floor Carpet
> 1 Wall Dry wall
> 1 Wall Paper
> 2 Floor Wood
> 2 Floor Carpet
>
> I 'd like to end up with results like:
> ID System Material
> 1 Floor Wood, Carpet
> 1 Wall Dry Wall, Paper
> 2 Floor Wood, Carpet

You probably want Proc REPORT, TABULATE or TRANSPOSE.

SQL is not the correct tool for what you want.

If you absolutely must use SQL it will involve a N reflexive join that is
limited at coding time where N is the expected maximum number of materials
in a system.

--
Richard A. DeVenezia
http://www.devenezia.com/