# Side-by-Side Transformation of Table Data

I have data that looks like the below. Note: the number of columns will vary and the number of rows will vary as well so the solution has to be dynamic and acount for that.

Co-Pay  |    Deductible   |   Co-Insurance
Plan A               10                  55                   140
Plan B               20                  75                    360
Plan C               30                  95                    779

and need the data to be transposed (NO AGGREGATION FUNCTIONS)

to look like this
Plan A   |     Plan B     | Plan C
Co-Pay                   10               20           30
Deductible              55               75           95
Co-Insurance       140             360          779
###### Who is Participating?

Commented:
>> transmitting data to middle-tier will be expensive
I don't understand why you think it will be cheaper if you turn the table on its side. It will be a lot more expensive on processor cycles in the database whereas I think it would be trivially easy to do in the presentation tier. But if you are determined to write some frightening SQL you could have fun with this:

SELECT
CASE num
WHEN 1 THEN 'CoPay'
WHEN 2 THEN 'Deductible'
WHEN 3 THEN 'CoInsurance'
END AS n,
MAX(CASE WHEN p=1 THEN CASE num
WHEN 1 THEN CoPay
WHEN 2 THEN Deductible
WHEN 3 THEN CoInsurance
END
END) AS p1,
MAX(CASE WHEN p=2 THEN CASE num
WHEN 1 THEN CoPay
WHEN 2 THEN Deductible
WHEN 3 THEN CoInsurance
END
END) AS p2,
MAX(CASE WHEN p=3 THEN CASE num
WHEN 1 THEN CoPay
WHEN 2 THEN Deductible
WHEN 3 THEN CoInsurance
END
END) AS p3
/* Repeat for whatever is the maximum number of plans */
FROM Numbers,
(
SELECT ROW_NUMBER() OVER (ORDER BY Pln) p,
CoPay, Deductible, CoInsurance
FROM tbl
)t
WHERE num BETWEEN 1 AND 3
GROUP BY num
ORDER BY num;

SELECT ROW_NUMBER() OVER (ORDER BY Pln) AS ColOrd,
Pln AS ColName
FROM tbl;
0

Commented:
You mean you just want to display the table on its side? Do that in your presentation or reporting tier. If you think you need to do it in the database for some reason then it might help to know what you are trying to achieve.
0

Author Commented:
I need to perform this in the database itself for performance reasons as the datasets in some cases will be very large and transmitting data to middle-tier will be expensive.  Trying to achieve side-by-side reports like (Consumer Reports).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.