How to Normalize or Parse Data into Columns?

Hi All,

Coming from an MS Access background, I used to parse multiple records per account into columns.  For example, the data would look like:

Account  DX  Seq

12345   333  1

12345  444   2

12345 555    3

67890 111    1

67890 222     2

67890 333     3

...and I would transform it into:

Account  DX1  DX2  DX3

12345    333   444  555

67890    111   222   333

using VBA attached to a Button Click event inside a Form.  Now that I'm using SQL Server 2005, I have a similar need to parse data into columns per above.  Since the query must be parameterized, I intend to use a stored procedure.  How would I structure the query to parse the diagnosis codes as I've shown above?

Thanks,

Sid 

Sid, sounds like what you want to look into is the PIVOT/UNPIVOT function: http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.90).aspx

Thanks for this link.  I had reviewed this functionality earlier but in my case I don't need to do any aggregation.  That is, I have a list of diagnoses and procedures that just need to be pivoted, without counting them, adding them etc.  Can PIVOT do this without including aggregation?

Try out this solution of using PIVOT without aggregation http://www.calsql.com/2009/10/pivot-with-out-aggregate.html

For future reference, you can always use MAX() or MIN() as your aggregate function in PIVOT, even with non-numeric data types.