How to Make a "Pivot" view?

What SQL do I need to say, pivot, a table? For example: I have a view that shows multiple rows of one record because it can be called differently:

ID Name
1 abdomen
1 abdominal
1 abdominal aorta


I would like to create a view that looks like:

ID Name 1 Name 2 Name 3
1 abdomen abdominal abdominal aorta

And how to you deal with the variable number of columns?

I am happy if I can just get:

ID Name string
1 abdomen,abdominal,abdominal aorta

Thanks in advance to your help!

Bounded pivot meaning that it doesn't address a variable of columns.

This code is written with the assumption that you know the maximum possible number of entries for a specific identifier (ID). This is the bounding condition to the pivot. It also assumes the presense of a subidentifier column (SUBID) in order to uniquely identify the different names for the same ID. It also assumes that the SUBID is a sequential whole number, starting with 1 for the first name and incrementing by one for each additional name up to the bounding value. It also assumes that SUBID is NOT NULL.

So rewriting your example table with these assumptions would yield:

ID SUBID Name
1 1 abdomen
1 2 abdominal
1 3 abdominal aorta

The query to produce a comma seperated list of up to N names having the same ID would be:

select ID,
MAX(SUBSTR(Name, 1, (1-ABS(SIGN(SUBID-1)))*LENGTH(Name)))
|| MAX(SUBSTR(', ' || Name, 1, (1-ABS(SIGN(SUBID-2)))*(LENGTH(Name)+2)))
|| MAX(SUBSTR(', ' || Name, 1, (1-ABS(SIGN(SUBID-3)))*(LENGTH(Name)+2))) "Name"
...
from table
group by ID

The ellipsis (...) means to repeat the expression for each possible SUBID up until you reach the maximum supported number. The only value that should change in each row if the number in the SIGN function, which should be set to the next higher value for each row, up until it reaches the maximum supported SUBID. The example above has the code for supporting 3 different names for the same ID beforer the ellipsis.

The +2 added to the LENGTH is needed for each SUBID>1 to accommodate the ', ' prefix catenated to the Name column value.

table obviously is the name of the table that you are querying.

The "Name" alias should only exist after the last expression.

This query worked on a quick data set that I threw together. The results I obtained were:

ID Name
----- ---------------------------------------
1 abdomen, abdominal, abdominal aorta

You should be able to modify the above query to get you the values in seperate rows instead of the comma seperated list.

I would be interested to learn of another way to accomplish bounded pivots if anyone else knows a neat technique. I would really be interested to learn a way to handle unbounded pivots, a technique that could handle a variable number of columns.


HTH,

Heath

[Edit: Removed characters that were interfering with page display]

[Edited by Heath on 04-19-2001 at 05:06 PM]