CODEWITHSUNDEEP
sqlsql-server
Anonymus User
Anonymus User

Reputation: 3

Arrange a single column data in multiple columns in SQL Server

I have the following table(raw data). I would like to see the data in following format(Formatted Data).

I tried the following query. It produces a rather weird result. Could someone guide me here how to achieve this.

declare @monthnames varchar(100)

    select distinct [Month], MonthNumber
    into #Months 
    from Table
    order by MonthNumber

SELECT @monthnames =   Stuff((SELECT ', ' + [Month]
    FROM #Months
    order by MonthNumber
    FOR XML PATH('')
    ), 1, 2, '')

declare @query varchar(500)

set @query = 'select CUR,' + @monthnames +
            ' from ' + 
            ' Pivot ( min(DATE) for [Month] in (' + @monthnames + ') ) as Pivottable ORDER BY CUR' 

EXEC (@query)

Upvotes: 0

Views: 90

Answers (2)

Squirrel
Squirrel

Reputation: 24793

You don't really need to use Dynamic SQL as you have a fixed number of pivoting columns.

What you are missing is the rn, so that each date appear under different row

SELECT  CUR, 
        [1] as Jan,
        [2] as Feb,
        [3] as Mar,
        [4] as Apr,
        [5] as May
FROM    (
            SELECT  CUR, MonthNumber, DATE, 
                    rn = ROW_NUMBER() OVER (PARTITION BY CUR, MonthNumber ORDER BY DATE)
            FROM    #Table
         ) AS d
        PIVOT
        (
            MIN(DATE)
            FOR MonthNumber IN ([1], [2], [3], [4], [5])
        ) AS p

Upvotes: 1

lptr
lptr

Reputation: 6798

declare @t table
(
CUR varchar(10),
[Month] varchar(20),
MonthNumber tinyint,
[Date] date
);

insert into @t(Cur, [Month], MonthNumber, [Date])
values
('AED', 'January', 1, '20200110'),
('AED', 'February', 2, '20200212'),('AED', 'February', 2, '20200215'),
('AED', 'March', 3, '20200305'),('AED', 'March', 3, '20200305'),('AED', 'March', 3, '20200305'),
('AED', 'April', 4, '20200402'),('AED', 'April', 4, '20200412'),('AED', 'April', 4, '20200415'),
('AED', 'June', 6, '20200619'),
('AED', 'August', 8, '20200801'),('AED', 'August', 8, '20200805'),('AED', 'August', 8, '20200810'), ('AED', 'August', 8, '20200824'),
----
('ARS', 'January', 1, '20200118'),
('ARS', 'April', 4, '20200416'),
('ARS', 'May', 5, '20200512'), ('ARS', 'May', 5, '20200513'), ('ARS', 'May', 5, '20200514'),
('ARS', 'September', 9, '20200902'),('ARS', 'September', 9, '20200922');


select CUR, [January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]
from
(
select CUR, [Date], dense_rank() over(partition by CUR, [Month] /*or MonthNumber*/ order by [Date]) as ranking,
    [Month]
from @t
) as t
pivot
(
min([Date]) for [Month] in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
) as pvt
order by CUR;

Upvotes: 0

Related Questions