在SQL Server中，将单列数据排列在多列中

Question

我有下表(原始数据(。我希望看到以下格式的数据(格式化数据(。

我尝试了以下查询。它产生了一个相当奇怪的结果。有人能在这里指导我如何做到这一点吗。

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) 

Answer 1

您实际上不需要使用Dynamic SQL，因为您有固定数量的数据透视列。

您缺少的是rn，因此每个日期都显示在不同的行下

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

Answer 2

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;