Post reply

Transposing rows into columns based on primary key

  • July 28, 2009 at 7:47 am

    #73305

    Hi

    I need to write a query to transpose rows into columns. My original data looks like this example:

    SysClientFileClaimWeekStatus

    11234113FI

    11234114FI

    11234115FI

    11234116FI

    11234117FI

    11234124FI

    11234125RO

    11234126RO

    11234127RO

    11234211OS

    11234212OS

    11234213FI

    11234214FI

    11234215FI

    11234216RE

    11234217RE

    15011111FI

    15011112FI

    15011113FI

    15011114NT

    15011115RO

    15011116RO

    15011117SV

    The primary key on the table is defined by the columns Sys, Client, File, Claim and Week. The columns Sys, Client, File and claim together denote a single claim and the Week column is there to define the status of the claim for that week. As can be seen, claims enter the table at differing weeks.

    Because there were so many rows in the table for each claim, I wrote a query comparing each week with the previous one to display only the first row of the claim if the status never changed, and to display every row where the status did change. The result looks like this:

    SysClientFileClaimWeekSt0St1Change

    11234113FIFIUnchanged

    11234125FIROChanged

    11234213OSFIChanged

    11234216FIREChanged

    15011114FINTChanged

    15011115NTROChanged

    15011117ROSVChanged

    Because I'm comparing each row with the previous one, the minimum week entered is now 2 instead of 1, but that is fine. What I need to do is write a query to convert the data into this form:

    SysClientFileClaimWk1St1Wk2St2Wk3St3Wk4St4

    11234113FI

    11234124FI5RO

    11234212OS3FI6RE

    15011112FI4NT5RO7SV

    That is, I need the Week number and Status of a claim in separate columns for each change in the Status. the original Status should have the Week number when the claim entered, and each change in Status should correspond to the Week number in which the change occurred.

    I have seen examples for transposing data into this form but only where the source data looks something like this:

    SysClientFileClaimWkNoWkStNoSt

    1123411Wk13St1FI

    1123412Wk14St1FI

    1123412Wk25St2RO

    1123421Wk12St1OS

    1123421Wk23St2FI

    1123421Wk36St3RE

    1501111Wk12St1FI

    1501111Wk24St2NT

    1501111Wk35St3RO

    1501111Wk47St4SV

    But I also don't know how I could get the data into this form.

    Please, any help would be greatly appreciated!!!

  • July 28, 2009 at 9:30 am

    #1031118

    Have you read these 2 articles?

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 28, 2009 at 10:53 am

    #1031176

    Yes, but to do that my data would have to be in the form of that last table I showed. Actually, the WkNo and StNo columns are not both needed, but rather just one column that serves as an identifier as to which Week and Status column the row would go. For example, the Index column below:

    SysClientFileClaimIndexWeekStatus

    112341113FI

    112341214FI

    1 12341225RO

    112342112OS

    112342123FI

    112342136RE

    150111112FI

    150111124NT

    150111135RO

    150111147SV

    The Quarter column in the article serves this purpose.

    My problem is that the status change always occurs at different weeks and all I know is that if there is a next change then that week number is greater than that of the previous change. Is there a way that I could number each row (as 1,2,3...) that has the same set of Sys, Client, File & Claim keeping the week column in ascending order? That would give me the Index column above that I need.

  • July 28, 2009 at 11:01 am

    #1031181

    This is hard to do without the table definition and sample data but you could try something with the row_number() function.

    SELECT data1, data2, data3, row_number() OVER (PARTITION BY data1, data2 ORDER BY data2)

    FROM temp

    just change the partition to be your primary key and the order by to be the week column

    If you can post the table create script and some insert statements to give some sample data.

  • July 28, 2009 at 12:54 pm

    #1031255

    Thanks a lot Matt, exactly what I needed!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply