Need help on TSQL - Transposing Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

balajikkrishnan - 18 Mar 2008 17:14 GMT

Hi SQL Folks,

I want to transpose rows into column based on a column1.

Here is my table

Column1    Column2    Column3    Column4    Column5
A123    1        EX1    01/12/2008
A123    2    EX1    EX2    01/13/2008
A123    3    EX2    EX3    01/15/2008
B657    1        EX17    01/23/2008
B657    2    EX17    EX55    01/25/2008
C567    1        EX1    01/12/2008
C567    2    EX1    EX5    01/12/2008
C567    3    EX5    EX17    01/15/2008
C567    4    EX17    EX89    01/22/2008

This is the output I want

T_Column1    T_Column2    T_Column3    T_Column4
A123    EX1    01/12/2008    01/13/2008
A123    EX2    01/13/2008    01/15/2008
A123    EX3    01/15/2008    GETUTCDATE()
B657    EX17    01/23/2008    01/25/2008
B657    EX55    01/25.2008    GETUTCDATE()
C567    EX1    01/12/2008    01/12/2008
C567    EX5    01/12/2008    01/15/2008
C567    EX17    01/15/2008    01/22/2008
C567    EX89    01/22/2008    GETUTCDATE()

THanks
BALAJI

Reply to this Message

balajikkrishnan - 18 Mar 2008 17:23 GMT

Column3 will be null value for all the first entry

Column1    Column2     Column3    Column4    Column5
A123    1    NULL    EX1    01/12/2008
A123    2    EX1    EX2    01/13/2008
A123    3    EX2    EX3    01/15/2008
B657    1    NULL    EX17    01/23/2008
B657    2    EX17    EX55    01/25/2008
C567    1    NULL    EX1    01/12/2008
C567    2    EX1    EX5    01/12/2008
C567    3    EX5    EX17    01/15/2008
C567    4    EX17    EX89    01/22/2008

>Hi SQL Folks,
>
[quoted text clipped - 28 lines]
>THanks
>BALAJI

Reply to this Message

Plamen Ratchev - 18 Mar 2008 17:32 GMT

Please do not multipost. Replied in 'programming':

SELECT F1.column1, F1.column4, F1.column5,
COALESCE(F2.column5, GETUTCDATE())
FROM Foo AS F1
LEFT JOIN Foo AS F2
ON F1.column1 = F2.column1
AND F1.column4 = F2.column3

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message