Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi
i have one table containing the code and date in rows

code date
101 12/2/2014
101 13/2/2014
101 17/2/2014
102 5/2/2014
102 11/2/2014
110 2/2/2014
110 5/2/2014
110 25/2/2014

i want to read the table row by row and based on the code the detail should be updated in another table like this.
code dt1 dt2 dt3 dt4
101 12/2/2014 13/2/2014 17/2/2014
102 5/2/2014 11/2/2014
110 2/2/2014 5/2/2014 25/2/2014

i need a help from you to complete this task.

advance thanks
Posted
Comments
Andrius Leonavicius 22-Feb-14 18:39pm    
Hi,

You could use Dynamic Pivot to achieve this. Please take a look at this link:
http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

Also, please take a look at this one (in order to understand how PIVOT and UNPIVOT works):
http://technet.microsoft.com/en-us/library/ms177410(v=sql.90).aspx

1 solution

This is an old question but I'm posting this solution to counter Solution 1 which uses a CURSOR - not a recommended approach (see SQL Server DO's and DONT's[^])

The following solution uses constructs available from SQL 2005 - Common Table Expressions and Pivot. Some CP articles that may be useful on those topics:
Common Table Expressions(CTE) in SQL SERVER 2008[^]
Simple Way To Use Pivot In SQL Query[^]

My sample data:
SQL
create table t1 
(
	code int,
	dt date
)

INSERT t1 values
(101,'12-FEB-2014'),
(101,'13-FEB-2014'),
(101,'17-FEB-2014'),
(102,'5-FEB-2014'),
(102,'11-FEB-2014'),
(110,'2-FEB-2014'),
(110,'5-FEB-2014'),
(110,'25-FEB-2014');
The query
SQL
WITH CTE1(code, dt, datenumber)
AS
(
    SELECT [code],dt,
     'dt' + CAST(ROW_NUMBER() over(PARTITION BY code
                         ORDER BY code, dt ASC) AS VARCHAR) datenumber
    FROM T1
)
select code, dt, datenumber
        into ##t2 from CTE1
SELECT *
FROM (
    SELECT
        code,
        dt,
        datenumber
    FROM ##t2
) as s
PIVOT
(
    MAX(dt)
    FOR [datenumber] IN ([dt1], [dt2], [dt3], [dt4])
)AS p
Results:
Code dt1        dt2         dt3         dt4
101 2014-02-12  2014-02-13  2014-02-17  NULL
102 2014-02-05  2014-02-11  NULL        NULL
110 2014-02-02  2014-02-05  2014-02-25  NULL
 
Share this answer
 
Comments
Abdulnazark 13-Mar-15 15:16pm    
thanks..
Abdulnazark 13-Mar-15 16:03pm    
declare @in varchar(max),@sql Nvarchar(max)

set @in=cast((select stuff((select ',['+datenumber +']' from ##t2 group by datenumber for xml path('')) ,1,1,'')) as varchar(max))

set @sql='SELECT *
FROM (
SELECT
code,
dt,
datenumber
FROM ##t2
) as s
PIVOT
(
MAX(dt)
FOR [datenumber] IN ('+@in+')
)AS p'
exec(@sql)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900