rows into columns from one table to another table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 22-Feb-14 0:45am

rajigopalan

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-03-13T06:39:00

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