try : use tempdb go if object_id('RYJL') Is not null Drop table RYJL Go CREATE TABLE [dbo].[RYJL]( [CBAH] [varchar](10) NULL, [DRYSJ] [varchar](100) NULL ) goinsert into RYJL SELECT '000274','2013-06-14' insert into RYJL SELECT '003114','2013-06-18' insert into RYJL SELECT '003114','2013-02-25' insert into RYJL SELECT '003114','2013-05-18' insert into RYJL SELECT '004080','2013-11-19' insert into RYJL SELECT '004134','2013-12-16';with cte_x as (select a.CBAH,a.DRYSJ,'DRYSJ'+rtrim(row_number() over(partition by a.CBAH order by a.DRYSJ)) x From RYJL a) select CBAH,[DRYSJ1],[DRYSJ2],[DRYSJ3],[DRYSJ4] from cte_x a pivot(max(DRYSJ) for x in([DRYSJ1],[DRYSJ2],[DRYSJ3],[DRYSJ4])) b/* CBAH DRYSJ1 DRYSJ2 DRYSJ3 DRYSJ4 --------------------------------------------- 000274 2013-06-14 NULL NULL NULL 003114 2013-02-25 2013-05-18 2013-06-18 NULL 004080 2013-11-19 NULL NULL NULL 004134 2013-12-16 NULL NULL NULL */
use tempdb
go
if object_id('RYJL') Is not null
Drop table RYJL
Go
CREATE TABLE [dbo].[RYJL](
[CBAH] [varchar](10) NULL,
[DRYSJ] [varchar](100) NULL
)
goinsert into RYJL SELECT '000274','2013-06-14'
insert into RYJL SELECT '003114','2013-06-18'
insert into RYJL SELECT '003114','2013-02-25'
insert into RYJL SELECT '003114','2013-05-18'
insert into RYJL SELECT '004080','2013-11-19'
insert into RYJL SELECT '004134','2013-12-16';with cte_x as
(select a.CBAH,a.DRYSJ,'DRYSJ'+rtrim(row_number() over(partition by a.CBAH order by a.DRYSJ)) x From RYJL a)
select CBAH,[DRYSJ1],[DRYSJ2],[DRYSJ3],[DRYSJ4]
from cte_x a
pivot(max(DRYSJ) for x in([DRYSJ1],[DRYSJ2],[DRYSJ3],[DRYSJ4])) b/*
CBAH DRYSJ1 DRYSJ2 DRYSJ3 DRYSJ4
---------------------------------------------
000274 2013-06-14 NULL NULL NULL
003114 2013-02-25 2013-05-18 2013-06-18 NULL
004080 2013-11-19 NULL NULL NULL
004134 2013-12-16 NULL NULL NULL
*/