Transposing rows in a one column into separate columns if data is different

Question

My table is:

and i want the output to be:

Answer 1

you can do this in this way, I did not testes hope it will works ;)

create table #Result (
    ID nvarchar(10),
    [Date] datetime,
    [DrugIndex] int,
    Drug1 nvarchar(1000))

DECLARE @ID nvarchar(10),
        @Date datetime,
        @Drug nvarchar(1000)
DECLARE crResult CURSOR FOR SELECT ID,[Date],[Drug] FROM tblSource 
OPEN crResult
FETCH NEXT FROM crResult INTO @ID,@Date,@Drug
WHILE @@FETCH_STATUS=0
    BEGIN 
        IF EXISTS(SELECT * FROM #Result WHERE ID=@ID AND [Date]=@Date)
            BEGIN
                DECLARE @Index int
                SET @Index=(SELECT DrugIndex FROM #Result WHERE Id=@Id and [Date]=@Date)
                SET @Index=@Index+1
                DECLARE @sqlCmd nvarchar(1024)
                SET @sqlCmd='ALTER TABLE #Result ADD Drug'+CONVERT(nvarchar(10),@Index)+' nvarchar(1000)'
                EXECUTE (@sqlCmd)
                SET @sqlCmd='UPDATE #Result SET [DrugIndex]='+CONVERT(nvarchar(10),@Index)+', [Drug'+CONVERT(nvarchar(10),@Index)+
                    ']='''+@Drug+''' WHERE ID='+CONVERT(nvarchar(10),@ID)+''' AND [Date]='''+CONVERT(nvarchar(20),@Date)+''''
                EXECUTE (@sqlCmd)
            END
        ELSE 
            INSERT INTO #Result(ID,[Date],DrugIndex,[Drug1]) VALUES @ID,@Date,1,@Drug 
        FETCH NEXT FROM crResult INTO @ID,@Date,@Drug
    END
CLOSE crResult
DEALLOCATE crResult

Answer 2

declare @t table (id int, dt date, drug varchar(100)); -- for example only
insert into @t values 
(1, '20160106', 'a'),
(1, '20160106', 'b'),
(1, '20160106', 'c'),
(1, '20160106', 'd'),
(1, '20160106', 'e'),
(1, '20160511', 'a'),
(1, '20160511', 'c'),
(1, '20160511', 'd');


if object_id('tempdb..#tmp') is not null drop table #tmp;

select *, 
       row_number() over (partition by dt order by 1/0) as rn
into #tmp
from @t; -- put here your table


declare @cols_drug varchar(4000) =
 stuff(
(select ',' + quotename(cast(rn as varchar(10)))  +' as drug' + cast(rn as varchar(10)) as [text()]
 from (select distinct rn from #tmp) t
 order by rn
 for xml path(''), type).value('.[1]', 'varchar(max)'), 1, 1, '');

 declare @cols_num varchar(4000) =
 stuff(
(select ',' + quotename(cast(rn as varchar(10))) as [text()]
 from (select distinct rn from #tmp) t
 order by rn
 for xml path(''), type).value('.[1]', 'varchar(max)'), 1, 1, '');



declare @sql varchar(8000) = 'select id, dt, ' + @cols_drug + 
' from #tmp t pivot (max(drug) for rn in (' + @cols_num + '))p';

exec(@sql);