Dynamically pivot table data without using aggregate function where columns are not consistent?

Question

I have read many articles but am still not able to get the required result.

I want to display all data for weight, pulsOx, bp and survey including redundant rows based on dates FromeDate and ToDate selected by user on front end. So columns will be pivoted for differnt dates between FromDate and ToDates are not consistent every time.

What changes should I make? Help soon please. Thanks in advance.

Here is my SQL stored procedure:

declare @Id int
declare @FromDatesp datetime
declare @ToDatesp datetime
declare @ProtocolGroupId int

set @Id=2
set @FromDatesp='2011/11/07'
set @ToDatesp='2012/2/29'
set @ProtocolGroupId=0

Declare @FromDate datetime
Declare @ToDate datetime

set @FromDate= convert(datetime,@FromDatesp,121)
set @ToDate=convert(datetime,@ToDatesp,121)

DECLARE @TableOfDates TABLE(DateValue DATETIME)

DECLARE @CurrentDate DATETIME

SET @CurrentDate = @FromDate

WHILE @CurrentDate <= @ToDate
BEGIN
    INSERT INTO @TableOfDates(DateValue) VALUES (@CurrentDate)

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

--SELECT convert(varchar(10),DateValue,111) FROM @TableOfDates

DECLARE @belowcols NVARCHAR(2000)
SELECT  @belowcols= STUFF(( SELECT DISTINCT
                                '],[' + convert(varchar(10), DateValue,111)
                        FROM    @TableOfDates AS t2  
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

                      print @belowcols

--DECLARE @abovecols NVARCHAR(2000)
--select @abovecols= replace ( @belowcols,'[','ISNULL([')
--select @abovecols = REPLACE(@abovecols,']','],0)')

Declare @Weightval varchar(MAX)
Declare @Pulseval varchar(MAX)
Declare @Oxval varchar(MAX)
Declare @BPSysval varchar(MAX)
Declare @BPDiasval varchar(MAX)
Declare @Alertval varchar(MAX)
Declare @Final varchar(MAX)
Declare @Question varchar(MAX)
Declare @Survey varchar(Max)

set @Weightval =''
set @BPSysval=''
set @BPDiasval=''
set @Pulseval =''
set @Oxval =''
set @Survey=''
declare @x int
DECLARE @PId varchar(500)
DECLARE @Config_XML varchar(1000)
DECLARE cur_Level CURSOR FOR 
select Id as PId,convert(varchar(1000),Config_XML)  from TblPatientCareplan where patientId=@Id and ProtocolGroupId=@ProtocolGroupId and Type='Reminder' and Parent_Id=(
select Id from TblPatientCareplan where patientId=@Id and ProtocolGroupId=@ProtocolGroupId and Type='Group' and IsDeleted=0) and IsDeleted=0
OPEN cur_Level
    FETCH NEXT FROM cur_Level INTO @PId, @Config_XML
    WHILE @@FETCH_STATUS = 0
    BEGIN
        print @PId 
        print @Config_XML 

            select @x=SUBSTRING(@Config_XML,15,2)
            print @x
            if(@x='18')
            begin 
            --set @Weightval ='SELECT ''weight;''+ convert(varchar(100),t1.Weight)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=1 order by alert_type asc),'''') as ''Value'',''1'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Weight'' as ''ItemType'',PatientId FROM TblWeightScaleData t1 JOIN (SELECT MAX(CreatedDt) Max_EnteredOn_By_Day FROM TblWeightScaleData where PatientId='''+convert(varchar(1000),@Id)+''' GROUP BY convert(varchar, CreatedDt, 112)) t2 ON t1.CreatedDt = t2.Max_EnteredOn_By_Day where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            set @Weightval ='SELECT ''weight;''+ convert(varchar(100),t1.Weight)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=1 order by alert_type asc),'''') as ''Value'',''1'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Weight'' as ''ItemType'',PatientId FROM TblWeightScaleData t1 where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '          
            end
            if(@x='19')
            begin 
            --set @BPSysval='SELECT ''sys;''+ convert(varchar(100),t1.Systolic)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=2 order by alert_type asc),'''') as ''Value'',''4'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Systolic'' as ''ItemType'',PatientId FROM TblBloodPressureData t1 JOIN (SELECT MAX(CreatedDt) Max_EnteredOn_By_Day FROM TblBloodPressureData where PatientId='''+convert(varchar(1000),@Id)+''' GROUP BY convert(varchar, CreatedDt, 112)) t2 ON t1.CreatedDt = t2.Max_EnteredOn_By_Day where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            --set @BPDiasval='SELECT ''dias;''+ convert(varchar(100),t1.Diastolic)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=2 order by alert_type asc),'''') as ''Value'',''5'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Diastolic'' as ''ItemType'',PatientId FROM TblBloodPressureData t1 JOIN (SELECT MAX(CreatedDt) Max_EnteredOn_By_Day FROM TblBloodPressureData where PatientId='''+convert(varchar(1000),@Id)+''' GROUP BY convert(varchar, CreatedDt, 112)) t2 ON t1.CreatedDt = t2.Max_EnteredOn_By_Day where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            set @BPSysval='SELECT ''sys;''+ convert(varchar(100),t1.Systolic)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=2 order by alert_type asc),'''') as ''Value'',''4'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Systolic'' as ''ItemType'',PatientId FROM TblBloodPressureData t1  where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            set @BPDiasval='SELECT ''dias;''+ convert(varchar(100),t1.Diastolic)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=2 order by alert_type asc),'''') as ''Value'',''5'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Diastolic'' as ''ItemType'',PatientId FROM TblBloodPressureData t1 where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            end
            if(@x='20')
            begin 
            --set @Pulseval ='SELECT ''pulse;''+ convert(varchar(100),t1.Pulse) +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=3 order by alert_type asc),'''') as ''Value'',''2'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Pulse'' as ''ItemType'',PatientId FROM TblPulseOxData t1 JOIN (SELECT MAX(CreatedDt) Max_EnteredOn_By_Day FROM TblPulseOxData where PatientId='''+convert(varchar(1000),@Id)+''' GROUP BY convert(varchar, CreatedDt, 112)) t2 ON t1.CreatedDt = t2.Max_EnteredOn_By_Day where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            --set @Oxval ='SELECT ''oxygen;''+ convert(varchar(100),t1.SpO2)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=3 order by alert_type asc),'''') as ''Value'',''3'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Oxygen'' as ''ItemType'',PatientId FROM TblPulseOxData t1 JOIN (SELECT MAX(CreatedDt) Max_EnteredOn_By_Day FROM TblPulseOxData where PatientId='''+convert(varchar(1000),@Id)+''' GROUP BY convert(varchar, CreatedDt, 112)) t2 ON t1.CreatedDt = t2.Max_EnteredOn_By_Day where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            set @Pulseval ='SELECT ''pulse;''+ convert(varchar(100),t1.Pulse) +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=3 order by alert_type asc),'''') as ''Value'',''2'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Pulse'' as ''ItemType'',PatientId FROM TblPulseOxData t1 where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            set @Oxval ='SELECT ''oxygen;''+ convert(varchar(100),t1.SpO2)  +'';''+convert(varchar(100),t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=3 order by alert_type asc),'''') as ''Value'',''3'' as ''DisplayOrder'', convert(varchar(10),t1.CreatedDt,111) as ''CreatedDt'',''Oxygen'' as ''ItemType'',PatientId FROM TblPulseOxData t1 where PatientId='''+convert(varchar(1000),@Id)+''' and CreatedDt  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '
            end         

        FETCH NEXT FROM cur_Level INTO @PId, @Config_XML
    END
CLOSE cur_Level
DEALLOCATE cur_Level

set @Survey='select ''-'' as ''Value'', ''5'' as ''DisplayOrder'' , CONVERT(varchar(10), getdate(), 111) AS ''CreatedDt'',''Survey'' AS ''ItemType'','''+convert(varchar(1000),@Id)+''' as ''PatientId''  from TblAlertMessage'

set @Question ='SELECT ''survey;''+ CONVERT(varchar(100), t1.Answer) + '';'' + CONVERT(varchar(100), t1.Id) +'';''+ ISNULL((select top(1) ISNULL((convert(varchar(100),Alert_Type)),0) from TblAlertMessage where Record_Id=t1.Id and DeviceTypeId=4 order by alert_type asc),'''') as ''Value'', ''6'' AS ''DisplayOrder'', CONVERT(varchar(10), t1.CreatedDate, 111) AS ''CreatedDt'',t1.Question AS ''ItemType'', t1.PatientId FROM dbo.TblProtocolGroupData AS t1 INNER JOIN (SELECT MAX(CreatedDate) AS Max_EnteredOn_By_Day FROM dbo.TblProtocolGroupData WHERE (PatientId='''+convert(varchar(1000),@Id)+''') AND (ProtocolGroupId = '''+convert(varchar(1000),@ProtocolGroupId)+''') GROUP BY Question, CONVERT(varchar, CreatedDate, 112)) AS t2 ON t1.CreatedDate = t2.Max_EnteredOn_By_Day WHERE PatientId='''+convert(varchar(1000),@Id)+''' AND (ProtocolGroupId = '''+convert(varchar(1000),@ProtocolGroupId)+''') and CreatedDate  between '''+ CONVERT(varchar(1000), @FromDate)+''' and '''+CONVERT(varchar(1000),@ToDate)+''' and IsDeleted=0  '


if(DATALENGTH(@Weightval)>3)
set @Weightval= ' union ' +@Weightval

if(DATALENGTH(@BPSysval)>3)
begin
set @BPSysval= ' union ' +@BPSysval
set @BPDiasval= ' union ' +@BPDiasval
end
if(DATALENGTH(@Pulseval)>3)
begin
set @Pulseval= ' union ' +@Pulseval
set @Oxval= ' union ' +@Oxval
end


print(@Weightval)
print(@BPSysval)
print(@BPDiasval)
print(@Pulseval)
print(@Oxval)



Set @Final=@Survey + ' union ' + @Question + @Weightval + @BPDiasval + @BPSysval + @Pulseval + @Oxval


DECLARE @query NVARCHAR(max)
SET @query = 'SELECT  ItemType, '+@belowcols +'


FROM    ( 

'+@Final+'

        )p PIVOT ( Max(Value)
                    FOR [CreatedDt] 
                      IN ('+
@belowcols +')
                  ) AS pvt order by DisplayOrder ;'

                  print(@query)

                  exec(@query)                                             

Answer 1

Just write several SQL statements and us an IF to determine at runtime which one to use.

Create Proc DoReport(@rptType int)
As
Begin


    if @rptType = 1 -- Group by col1
    Begin
        -- Sql for group by col1 here.

    end else if @rpttype = 2 -- group by col2
    begin
        -- Sql for group by col2 here.


    end


End