我意识到这个问题以前已经以多种形式被问过,但我正在努力将我找到的每一个答案应用到我的特定表格中。从一开始--直奔细节。
表A有四列(唯一ID、编号、修订、日期)。当执行select * from A时,结果集如下:
ID | Number | Revision | Date
------------------------------------
01 | 12345 | 1 | 01/01/2010
02 | 12345 | 2 | 01/04/2010
03 | 123 | 1 | 01/12/2010
04 | 1234 | 1 | 09/09/2012
05 | 12345 | 3 | 09/12/2012我希望每次修订的日期都在每个数字的一行中,例如,对于数字12345,查询将返回
ID | Number | Revision | Date | Revision 2 | Revision 2 Date | Revision 3 | Revision 3 Date |
---------------------------------------------------------------------------------------------
01 | 12345 | 1 | 01/01/2010 |2 | 01/04/2010 | 3 | 09/12/2012|我知道它的语法类似于
SELECT Revision, Date
FROM (
SELECT NUMBER
FROM A) as B INNER JOIN ON a.Number = B.Number
GROUP BY a.Number但是,这仍然会为每个组合返回一行,我只想要上面显示的结果。
如果有任何建议或提示,我将不胜感激!
发布于 2012-10-08 21:14:24
您可以先使用UNPIVOT,然后使用PIVOT来完成此任务。如果您有已知数量的值要转换为列,则可以使用静态PIVOT并对这些值进行硬编码:
select number, [revision_1], [date_1],
[revision_2], [date_2],
[revision_3], [date_3]
from
(
select number, value,
col + '_' + cast(rn as varchar(10)) col
from
(
select id, number, cast(revision as varchar(10)) revision,
convert(varchar(10), date, 120) date,
row_number() over(partition by number order by date) rn
from yourtable
) x
unpivot
(
value
for col in (revision, date)
) u
) x1
pivot
(
max(value)
for col in ([revision_1], [date_1],
[revision_2], [date_2],
[revision_3], [date_3])
)p如果您有一个未知数量的值,那么您将需要使用dynamic SQL来PIVOT数据。
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT ','
+ quotename(c.name + '_'
+ cast(t.rn as varchar(10)))
from
(
select row_number()
over(partition by number order by date) rn
from yourtable
) t
cross apply
sys.columns as C
where C.object_id = object_id('yourtable') and
C.name in ('Revision', 'date')
group by c.name, t.rn
order by t.rn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select number, value,
col + ''_'' + cast(rn as varchar(10)) col
from
(
select id, number, cast(revision as varchar(10)) revision,
convert(varchar(10), date, 120) date,
row_number() over(partition by number order by date) rn
from yourtable
) x
unpivot
(
value
for col in (revision, date)
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
exec(@query)https://stackoverflow.com/questions/12782349
复制相似问题