I want to change my data from this:

to this:

1. Remove the duplicate values for each ID (see ID=2245873),
2. List the dates associated with each ID in a row,

I don't know how many dates each ID has so the number of columns I need is unknown, is this possible?

I also need to be able to merge this new table with another, so it needs to be a view or alter table?

If there are no more dates associated with an ID I want the cell to be null

Table name: dbo.rem

CodePudding user response：

Does this work?

Trying to do a dynamic PIVOT similar to the other post linked above, but adding a field using 'Day' as a string and DENSE_RANK to determine the output columns (Date1, Date2, Date3...)

This is used both to set the @cols variable, and within the SELECT statement in brackets - where the resultant field is named [xdate].

When pivoted, it is these values that appear as column names alongside your original ID, then populated with specific dates relevent to that ID... hopefully!

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ','   QUOTENAME('Date'  CAST(DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [date]) AS VARCHAR(MAX)) ) 
                    from sourcetable yt
                    group by [ID], [date]
                    
                 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [ID],'   @cols   ' from 
             (
                select [ID], [Date], ''Date''  CAST(DENSE_RANK() OVER (PARTITION BY [ID] ORDER BY [date]) AS VARCHAR(MAX)) as [xdate]
                from sourcetable yt
            ) x
            pivot 
            (
                MAX([date] )
                for  [xdate] in ('   @cols   ')
            ) p '
            

    

EXECUTE(@query)
GO

CodePudding user response：

I have taken the dynamic pivot columns formation part from the below answer

dynamic pivot query

The below logic should work

declare @tbl table(id int, date varchar(50))

insert into @tbl values(2245873,'03-Jan')
,(2245873,'03-Jan'),(2245873,'04-Jan')
,(8394313,'03-Jan'),(8394313,'05-Jan'),(8394313,'07-Jan')

select distinct * into #temp
--,ROW_NUMBER()over(order by id) rownum
from @tbl

-- This part is to generate row numbers and form the dates

select id,date,
'date'   convert(varchar,rownum) as 'datetobepivoted' into #temp1
from(
select *,ROW_NUMBER()over(partition by id order by id) rownum from #temp
)t


declare @pivotcols varchar(200) = (STUFF((SELECT distinct ','   QUOTENAME(c.datetobepivoted) 
            FROM #temp1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,''))
declare @query varchar(max)

set @query = '
SELECT id, '   @pivotcols   ' from 
(select id,date,datetobepivoted from #temp1)t
pivot(
max(date) for datetobepivoted in ('   @pivotcols   '))t1

'

exec(@query)

drop table #temp
drop table #temp1

Note: max aggregate function works on varchar too !