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?
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.