0

i have an output temporary table created with a sql stored procedure. it will list total days for types of insurances listing them in seperate fields here is the abbreviate version:

patient insurance qtydays
AAA       MCare     19
AAA       MCaid     07
AAA       Private   21

what id like to see is the following:

patient MCaredays MCaidDays PrivateDays
AAA         19        07        21

Im guessing PIVOT would work but cant get it to work any help is appreciated!

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
angelo
  • 5
  • 2
  • I second @Pரதீப்'s question, you'd logically want to `group by` patient and then create a new `insurance_nameDay` field that's a sum of the insurance days. But if there's an indefinite numbers of insurances I have no clue of how to do it. (There's obviously a way, but not that I know of) – Cyril Lemaire Jan 15 '18 at 16:42

1 Answers1

0

You could try like this;

SELECT patient, MCare, MCaid,Private
FROM (
SELECT patient, insurance, qtydays
FROM YourTable) up
PIVOT (SUM(qtydays) FOR insurance IN (MCare, MCaid, Private)) AS pvt
lucky
  • 12,734
  • 4
  • 24
  • 46