Convert Row Data in 1 Field to Many Columns

  • Hello this is my first post and my TSQL is average at best so any help will be much appreciated

    I want to create a view on a SQL 2005 Table that contains the data I need but the data is currently in the wrong layout.

    What I mean is there are 2 fields (SerialBox, SerialNo) which currently have the format below :-

    SerialBox SerialNo

    Box1 Serial1

    Box1 Serial2

    Box1 Serial3

    Box1 Serial4

    Box1 Serial5

    Box1 Serial6

    Box1 Serial7

    Box2 Serial1

    Box2 Serial2

    etc etc

    What I want is

    SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7

    Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Can this be done in a View ?

    Thanks,

    Craig

  • Are you trying to create a crosstab of some type? Or are you trying to create something that looks like labels? Both are reasonably easy in SSRS.

    You can create crosstabs in T-SQL... Jeff Moden posted examples in the Articles section... here's one:

    Jeff Moden's Crosstabs Article[/url]

    If you're using SSRS, you can use the Matrix wizard... although it's not clear what the non-first rows and columns represent from your example. What do they represent? In a crosstab, they're some kind of summary function: SUM, AVG, MIN, MAX... etc.

  • Are you looking for pivot/unpivot?

  • I think this might be what you need:

    ;WITH CTE AS

    (

    SELECT DISTINCT

    SerialBox

    FROM Boxes B

    ),

    CTE1 AS

    (

    SELECT CTE.SerialBox,

    STUFF(A.SerialNos,1,1,'') SerialNos

    FROM CTE

    CROSS APPLY(SELECT ',' + B1.SerialNo

    FROM Boxes B1

    WHERE B1.SerialBox = CTE.SerialBox

    FOR XML PATH ('')) AS A(SerialNos)

    )

    SELECT *

    FROM CTE1

  • Thanks for the information,

    1st day back in work today so will take a look and let you know how I get on

  • Japster (4/2/2014)


    What I want is

    SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7

    Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Can this be done in a View ?

    Thanks,

    Craig

    were you expecting that each serialno was in a separate column?

    am thinking you made to need to use dynamic SQL

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • This looks like a cookie cutter cross tab to me. Please see the articles in my signature about cross tabs. Also, here is a link to another thread where I posted a solution to a very similar type of problem earlier today.

    http://www.sqlservercentral.com/Forums/Topic1559371-392-1.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • J Livingston SQL (4/8/2014)


    Japster (4/2/2014)


    What I want is

    SerialBox SerialNo1 SerialNo2 SerialNo3 SerialNo4 SerialNo5 SerialNo6 SerialNo7

    Box1 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box2 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Box3 Serial1 Serial2 Serial3 Serial4 Serial5 Serial6 Serial7

    Can this be done in a View ?

    Thanks,

    Craig

    were you expecting that each serialno was in a separate column?

    am thinking you made to need to use dynamic SQL

    Yes was expecting the SerialNo's to be in seperate columns. I did post it that way in the original post but it's stripped out the spaces and squished it up

    Thanks

  • Is there a maximum number of distinct serial numbers or are there at most 7 like in your example?

  • Hi,

    thanks for the reply, for this exercise there will always be 7 serials per box.

    However there is data in the table that has 42 serials in a box, will that cause a problem ?

    Thanks

  • Japster (4/9/2014)


    Hi,

    thanks for the reply, for this exercise there will always be 7 serials per box.

    However there is data in the table that has 42 serials in a box, will that cause a problem ?

    Thanks

    Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • So this is your homework?

  • Sean Lange (4/9/2014)


    Japster (4/9/2014)


    Hi,

    thanks for the reply, for this exercise there will always be 7 serials per box.

    However there is data in the table that has 42 serials in a box, will that cause a problem ?

    Thanks

    Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.

    Yes I looked at the link you provided yesterday and I still don't get it, sorry my SQL skills are weak :ermm:

  • Japster (4/9/2014)


    Sean Lange (4/9/2014)


    Japster (4/9/2014)


    Hi,

    thanks for the reply, for this exercise there will always be 7 serials per box.

    However there is data in the table that has 42 serials in a box, will that cause a problem ?

    Thanks

    Nope. Look at the example I linked yesterday. It will show exactly how to do this kind of thing.

    Yes I looked at the link you provided yesterday and I still don't get it, sorry my SQL skills are weak :ermm:

    OK. Then I am not going to post the code because you need to understand any code that you use. Let's break this down into pieces to help you understand what is going on.

    The first step was to create a cte to allow us to number the rows. Do you understand the cte I used? Did you understand why I did that?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 14 posts - 1 through 13 (of 13 total)

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