Convert static PIVOT to dynamic PIVOT on 2 columns

[waubain]

I am trying to change my static PIVOT to a dynamic 2 column PIVOT as the report now needs to span over varying periods of time up to one year.

My data initially looks like this for 3 months.
[tt]
AbxDate Antibiotic DDD DOT
1/1/2015 AMPICILLIN 82.0 24.5
1/1/2015 CLINDAMYCIN 9.3 9.0
1/1/2015 VANCOMYCIN 74.8 94.7
2/1/2015 AMPICILLIN 3.6 2.4
2/1/2015 CLINDAMYCIN 2.6 4.0
2/1/2015 VANCOMYCIN 47.8 65.5
3/1/2015 AMPICILLIN 14.1 4.3
3/1/2015 CLINDAMYCIN 3.4 4.3
3/1/2015 VANCOMYCIN 62.3 79.7
[/tt]
I want my output to look like this
[tt]
Antibiotic Jan15_DOT Jan15_DDD Feb15_DOT Feb15_DDD Mar15_DOT Mar15_DDD
AMPICILLIN 24.5 82.0 2.4 3.6 4.3 14.1
CLINDAMYCIN 9.0 9.3 4.0 2.6 4.3 3.4
VANCOMYCIN 94.7 74.8 65.5 47.8 79.7 62.3
[/tt]
I am currently using this SQL I adapted from the web to static pivot the data. I see examples of dynamic pivots, but I have not seen anything on 2 columns that is at my level of understanding.

SELECT *
FROM
(
  SELECT DISTINCT
	Antibiotic
	,CAST(FORMAT(AbxDate, 'MMM', 'en-US')+RIGHT(YEAR(AbxDate), 2) AS varchar(5))+CHAR(95)+col AS new_col
    ,value
  from #asp
  CROSS APPLY
  (
    VALUES
        (DOT, 'DOT'),
        (DDD, 'DDD')
   ) x (value, col)
) src
PIVOT
(
  MAX(value)
  FOR new_col in (Jan15_DOT, Jan15_DDD, Feb15_DOT, Feb15_DDD, Mar15_DOT, Mar15_DDD)
) piv

Any help would be appreciated.


You don't know what you don't know...

 

[waubain]

After reviewing examples and since I had the option, I decided it was simpler to restructure my input data.

IF OBJECT_ID('TEMPDB..#ddd') IS NOT NULL DROP TABLE #ddd
IF OBJECT_ID('TEMPDB..#dot') IS NOT NULL DROP TABLE #dot

--create receiving temp tables
CREATE TABLE #ddd    (AbxDate  date, Antibiotic varchar(50), MType varchar(3), Mvalue decimal(10,1)) 
INSERT INTO #ddd VALUES
('1/1/2015', 'AMPICILLIN', 'DDD', 82),
('1/1/2015',  'CLINDAMYCIN', 'DDD', 9.3),
('1/1/2015',  'VANCOMYCIN', 'DDD', 74.8),
('2/1/2015',  'AMPICILLIN', 'DDD', 3.6),
('2/1/2015',  'CLINDAMYCIN', 'DDD', 2.6),
('2/1/2015',  'VANCOMYCIN', 'DDD', 47.8),
('3/1/2015',  'AMPICILLIN', 'DDD', 14.1),
('3/1/2015',  'CLINDAMYCIN', 'DDD', 3.4),
('3/1/2015',  'VANCOMYCIN', 'DDD',62.3)

--create receiving temp tables
CREATE TABLE #dot    (AbxDate  date, Antibiotic varchar(50), MType varchar(3), MValue decimal(10,1)) 
INSERT INTO #dot VALUES
('1/1/2015', 'AMPICILLIN', 'DOT', 24.5),
('1/1/2015',  'CLINDAMYCIN', 'DOT', 9.0),
('1/1/2015',  'VANCOMYCIN', 'DOT', 94.7),
('2/1/2015',  'AMPICILLIN', 'DOT', 2.4),
('2/1/2015',  'CLINDAMYCIN', 'DOT', 4.0),
('2/1/2015',  'VANCOMYCIN', 'DOT', 65.5),
('3/1/2015',  'AMPICILLIN', 'DOT', 4.3),
('3/1/2015',  'CLINDAMYCIN', 'DOT',  4.3),
('3/1/2015',  'VANCOMYCIN', 'DOT',  79.7)


IF OBJECT_ID('TEMPDB..#asp') IS NOT NULL DROP TABLE #asp
declare @sql as varchar(max)
declare @columns as varchar(max)
SELECT 
	AbxDate
	,Antibiotic
	,MValue
	,CAST(FORMAT(AbxDate, 'MMM', 'en-US')+RIGHT(YEAR(AbxDate), 2) AS varchar(5))+CHAR(95)+[MType] AS [ColumnName]

INTO #asp
FROM #ddd
UNION ALL
SELECT 
	AbxDate
	,Antibiotic
	,MValue
	,CAST(FORMAT(AbxDate, 'MMM', 'en-US')+RIGHT(YEAR(AbxDate), 2) AS varchar(5))+CHAR(95)+[MType] AS [ColumnName]
 FROM #dot

SELECT @columns = 
       COALESCE(@columns+',','')+QUOTENAME(ColumnName)
       FROM(
              SELECT DISTINCT AbxDate, ColumnName
              FROM #asp
              ) AS b
              ORDER BY b.AbxDate, b.ColumnName

SET @sql = 'SELECT Antibiotic, ' + @columns + ' FROM
(                 
 SELECT Antibiotic,  ColumnName, MValue               
 FROM #asp 
) x 
PIVOT            
( 
 MAX(MValue)
 FOR ColumnName in (' + @columns + ')             
 ) p 
 ORDER BY Antibiotic ' 
EXECUTE(@sql)

You don't know what you don't know...