0

I am trying to figure out what would be the best way in SQL or SSIS to pivot my results. I am pretty new to pivoting data and need some direction.

Here are my query results:

    SELECT [ID]
      ,[Medication]
      ,[Route]
      ,[Day]
      ,Sum(Cast(Dose as float) as TotalDose
      ,Medication + '_' + Route + '_' + Cast(Day as varchar(10)) as PivotKey
  FROM Table1
  group by ID, Medication, Route, Day

ID  Medication    Route Day TotalDose  PivotKey

1   Tylenol       IV    1   1500       Tylenol_IV_1
1   Tylenol       PO    1   1300       Tylenol_PO_1
1   Tylenol       PO    2   1950       Tylenol_PO_2
2   Aspirin       PO    1   1000       Aspirin_PO_1
2   Aspirin       PO    2   500        Aspirin_PO_2

I have an existing table that stores these PivotKeys as columns, or there is always an option I guess to create a new table based on the data in PivotKey. I am trying to make the results look as follows:

ID   Tylenol_IV_1   Tylenol_IV_2   Tylenol_PO_1   Tylenol_PO_2   Aspirin_PO_1   Aspirin_PO_2

----------

 1   1500                0             1300           1950           0                0
 2     0                 0               0              0           1000             500

Thanks for the suggestions!

ssokol91
  • 552
  • 3
  • 13
  • 25
  • If you don't know the values ahead of time, you'll need a dynamic sql pivot solution. I have a setup, it's a bit overkill, but it maintains pivot views into these tables when run (so could be set up as a job). – Jaaz Cole Jun 23 '14 at 18:15
  • 1
    Or, check this out. http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Jaaz Cole Jun 23 '14 at 18:20
  • The only values I don't know are the dosages. I do know that Days will always be 1 & 2, and i know it will always be for this set of Meds and Routes. – ssokol91 Jun 23 '14 at 18:21

0 Answers0