[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

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
0
ampatel007
Asked:
ampatel007
  • 2
1 Solution
 
dportasCommented:
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
 
ampatel007Author 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
 
dportasCommented:
>> 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now