SQL Merge duplicate rows

Question

I want to change my data from this:

ID	Date
2245873	03-JAN
2245873	03-JAN
2245873	04-JAN
8394313	03-JAN
8394313	04-JAN
8394313	05-JAN
3446512	31-DEC
3446512	20-JAN
617828	31-DEC
617828	03-JAN
617828	20-JAN
61342	02-JAN

to this:

ID	date1	date2	date3
2245873	03-JAN	04-JAN
8394313	03-JAN	04-JAN	05-JAN
3446512	31-DEC	20-JAN
617828	31-DEC	03-JAN	20-JAN
61342	02-JAN

Remove the duplicate values for each ID (see ID=2245873),
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

To remove duplicates you can use DISTINCT-CLAUSE SELECT DISTINCT ID,Date from dbo.rem. Unknown amount of columns requires, in general, dynamic pivot query — Sergey, Feb 18 '22 at 14:26
Please avoid posting [images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of data, sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). See the [question guide](https://stackoverflow.com/help/how-to-ask). — Stu, Feb 18 '22 at 14:29
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Stu, Feb 18 '22 at 14:29
@Stu Thank you, but that query is slightly different as the number of date columns is unknown and unmapped on my data — CodeBob, Feb 18 '22 at 14:34
@GraceTrif take a look at what Stu said. it supports dynamic columns as well. — sajjad rezaei, Feb 18 '22 at 14:56
@sajjadrezaei I'm not sure how to structure Stu's recommendations — CodeBob, Feb 18 '22 at 15:07
This is a SQL Anti-Pattern. The way your data is structured to start with is how data is ***meant*** to be in SQL *(both the language and the database engines are built around that concept)*. If you transform the data you have to deal with sparse columns and the associated NULLs, maintenance becomes worse, and persisting that data in tables or consuming it in subsequent SQL queries requires yet more Dynamic SQL. If this is for presentation to humans, the database is the wrong place to format the data, do it in your application / reporting layer. So, ***why*** would you want to do this? — MatBailie, Feb 18 '22 at 16:27
`I also need to be able to merge this new table with another, so it needs to be a view or alter table?` Then do ***not*** do this. Merge the data from your starting structure. If you're unsure how to do ***that*** then raise a new question and I guarantee we'll solve that without dynamic pivots. https://en.wikipedia.org/wiki/XY_problem — MatBailie, Feb 18 '22 at 16:30
@MatBailie I want to do this so I can merge it to another table which also has multiple entries for each ID (this time purchase dates). I need each of theses dates to be matched to each of the IDs purchase dates. I can't merge the two tables then merge the duplicated rows as lots of data's would be lost from the merge i.e. SQL would map each ID to the first ID in the other table and the 2nd, 3rd, ... entries for each ID in the original table would be lost. So I need to merge this table so I only have one row for each ID then merge that to my other table — CodeBob, Feb 21 '22 at 12:05
If you already have a table, you already have a fixed number of columns. Also, you're assuming you can't update one structure using the other, this is based on faulty reasoning; "If I don't know how, then it can't be done". If you create a new question with the actual scenario, I guarantee people can help you *(what the target table looks like, what the source table looks like, and what operations you need to perform)*. Either way, targeting a dynamic number of columns, and so using dynamic sql for this, is a SQL Anti-Pattern. It's ***not*** how SQL is designed to be used. — MatBailie, Feb 21 '22 at 12:53
@GraceTrif You could try my answer out. I've modified it to bring the output in the exact way you need — Coder1991, Feb 22 '22 at 05:58

score 1 · Answer 1 · answered Feb 18 '22 at 15:57

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

Coder1991 · Accepted Answer · 2022-02-22T05:54:22.460

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 @pivotcolstbl varchar(200) = (STUFF((SELECT distinct ',isnull(' + QUOTENAME(c.datetobepivoted) + ', '''') ' + c.datetobepivoted 
            FROM #temp1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,''))


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, ' + @pivotcolstbl + ' 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 !

SQL Merge duplicate rows

2 Answers2