Converting rows to columns

  • Hello

    I have a table with the following data

    00001315, 10a

    00001315, 1a

    00001315, 10b

    00001315, 6

    00001315, 2b

    I need to be able to transform the data to one row

    00001315, 10a,1a,10b,6,2b

    I have had a look at the PIVOT function but am not able to get it to work

    Can anyone please offer a solution

    SQL Server 2008r2/2008/2005

    Many Thanks

  • If you truly mean to convert row to "columns" as you say...

    http://www.sqlservercentral.com/articles/t-sql/63681/

    If you actually meant convert the rows to a single, comma delimited column as you've shown...

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Last but not least, GOOGLE is your friend. 🙂

    http://www.google.com/search?hl=en&source=hp&biw=1229&bih=568&q=converting+rows+to+columns+in+sql&oq=coverting+rows+to+colu&aq=2sx&aqi=g-sx10&aql=1&gs_sm=c&gs_upl=2125l7172l0l9344l22l14l0l0l0l0l828l3408l2-6.2.1.0.1l10l0

    If you'd like a coded example for your specific data, please provide coded data. See the first link in my signature below for how to do that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you for your reply 🙂

    I have tried the pivot table approach but neither column lends itself to an aggregate function and unfortunatly my skill set with SQL is limited

    SELECT 'BAForeignKey' BA, CAST([0] AS VARCHAR(50)) AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5

    FROM

    ( SELECT cast(BAForeignKey AS varchar(50))AS BAForeignKey, BldClass FROM ApplicationBldTypes WHERE BAForeignKey = '00001315') AS p

    PIVOT

    ( count(BAForeignKey)

    FOR BldClass IN ([0],[1],[2],[3],[4])

    ) AS pvt

    this query just returns

    BAForeignKey,0 ,0,0,0,0

    The select case approach is complex as the number of variations in build class (2nd column) can be > 100

    Actually I only require 5 columns of data even if my source table has 25 rows, the first column of my source data will always be the same

    I undertsand a little about using dynamic SQL to only retieve the distinct bldClass from the source and then use a case statement

    but then....

    Many Thanks

  • andre-394971 (10/23/2011)


    I have tried the pivot table approach but neither column lends itself to an aggregate function and unfortunatly my skill set with SQL is limited

    Heh... use MAX instead... it's an aggregate function, as well. 😉 Trust me... it works.

    Again, if you want a coded answer, please see the article at the first link in my signature below for how to post the data in a readily consumable format. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Back again I have coded my question as suggested 🙂

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    CREATE TABLE #mytable

    ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    BARef VARCHAR(50),

    BLDClass VARCHAR(50)

    )

    INSERT INTO #mytable

    (BAREf,BLDClass)

    SELECT '00001315','10a' UNION all

    SELECT '00001315','1a' UNION all

    SELECT '00001315','10b' UNION all

    SELECT '00001315','1b' UNION all

    SELECT '00001315','9a'

    SELECT * FROM #mytable

    SELECT 'BARef', CAST([0] AS VARCHAR(50)) AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5

    FROM

    ( SELECT BARef, BldClass FROM #mytable WHERE BARef = '00001315') AS p

    PIVOT

    ( max(p.BARef)

    FOR BldClass IN ([0],[1],[2],[3],[4])

    ) AS pvt

    I end up with

    BARef,Null,Null,Null,Null,Null

    Can anyone offer tell how to get

    00001315,10a,1a,10b,1b,9a

    The order in which I have shown the result is important

    I will only ever require 6 columns regardless of how many records are returned from the table

    Many Thanks

  • I added to your data (below) so you can see this actually works...

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    CREATE TABLE #mytable

    ( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    BARef VARCHAR(50),

    BLDClass VARCHAR(50)

    )

    INSERT INTO #mytable

    (BAREf,BLDClass)

    SELECT '00001315','10a' UNION all

    SELECT '00001316','abc' UNION all

    SELECT '00001315','1a' UNION all

    SELECT '00001316','456' UNION all

    SELECT '00001315','10b' UNION all

    SELECT '00001316','xyz' UNION all

    SELECT '00001315','1b' UNION all

    SELECT '00001316','123' UNION all

    SELECT '00001316','01a' UNION ALL

    SELECT '00001315','9a'

    SELECT * FROM #mytable

    And, like I said, use MAX... and the data in each row is in order according to the value stored in the ID column...

    WITH

    cteEnumerate AS

    (

    SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY BAREf ORDER BY ID),

    BAREf,

    BLDClass

    FROM #mytable

    )

    SELECT BAREf,

    BCAClass1 = MAX(CASE WHEN RowNum = 1 THEN BLDClass ELSE '' END),

    BCAClass2 = MAX(CASE WHEN RowNum = 2 THEN BLDClass ELSE '' END),

    BCAClass3 = MAX(CASE WHEN RowNum = 3 THEN BLDClass ELSE '' END),

    BCAClass4 = MAX(CASE WHEN RowNum = 4 THEN BLDClass ELSE '' END),

    BCAClass5 = MAX(CASE WHEN RowNum = 5 THEN BLDClass ELSE '' END)

    FROM cteEnumerate

    GROUP BY BAREf

    ORDER BY BAREf

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ok Thanks Ill Give your suggestion a try

    Finally got the pivot to work 🙂

    Im puzzled as too why the following is happening

    This works...

    There are only three records returned from dbo.ApplicationBldTypes

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    CREATE TABLE #mytable

    ( RowId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    BAForeignKey VARCHAR(8),

    BLDClass VARCHAR(50)

    )

    INSERT INTO #mytable

    SELECT BAForeignKey, BldClass FROM dbo.ApplicationBldTypes WHERE BAForeignKey = '00001315'

    SELECT [0] AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5,[5] AS bcaclass6

    FROM

    ( SELECT TOP(5) RowId, BldClass FROM #mytable WHERE BAForeignKey = '00001315' ORDER BY RowId) AS p

    PIVOT

    ( max(p.BldClass)

    FOR RowId IN ([0],[1],[2],[3],[4],[5])

    ) AS pvt

    Gives Me

    BCAClass1 BCAClass2 bcaclass3 bcaclass4 bcaclass5bcaclass6

    NULL 1a 10b 10a Null Null

    Which is OK

    BUT THIS Does NOT...the difference is Im actually using my live database eg #MyTable is replaced by dbo.ApplicationBldTypes

    SELECT [0] AS BCAClass1 ,[1] AS BCAClass2,[2] AS bcaclass3,[3] AS bcaclass4,[4] AS bcaclass5,[5] AS bcaclass6

    FROM

    ( SELECT TOP(5) RowId, BldClass FROM dbo.ApplicationBldTypes WHERE BAForeignKey = '00001315' ORDER BY RowId ) AS p

    PIVOT

    ( max(p.BldClass)

    FOR RowId IN ([0],[1],[2],[3],[4],[5])

    ) AS pvt

    Gives Me

    BCAClass1 BCAClass2 bcaclass3 bcaclass4 bcaclass5bcaclass6

    NULL NULL NULL NULL NULL NULL

  • Hello Again

    I have tried your suggetion on the live database and it works...many thanks....:-)

    Now I need to be able to incorperate the result row returned by your suggestion into a result row from a normal query.

    EG The normal query, which will only ever return one row, goes like this

    Select Name,Address,Postcode..etc and then I need to add the five columns returned from your suggestion

    From ......

    Where BAForeignKey = '00001315'

    Not sure what the best appoach would be ?

    Many thanks

  • Ummmm... none of this is going to work correctly unless the dbo.ApplicationBldTypes has either an IDENTITY column or a "DateCreated" column (of some sort) to preserve the order of the individual elements on each row in the result set. Does the dbo.ApplicationBldTypes table have such a thing?

    If not, are you sure that the order of the 5 columns of data actually matters?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sorry That was remiss of me the ApplicationBuildingTypes table has a RowId (int) column which is the primary key

  • Then you don't even need a Temp Table. I'll be back in a bit.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sorry for the delay... there were a couple of posts ahead of you...

    --Pretend that this is your dbo.ApplicationBldTypes table...

    IF OBJECT_ID('TempDB..#ApplicationBldTypes','U') IS NOT NULL

    DROP TABLE #ApplicationBldTypes

    ;

    CREATE TABLE #ApplicationBldTypes

    (

    RowId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    BAForeignKey VARCHAR(8),

    BLDClass VARCHAR(50)

    )

    ;

    INSERT INTO #ApplicationBldTypes

    (BAForeignKey, BLDClass)

    SELECT '00001315','10a' UNION all

    SELECT '00001316','abc' UNION all

    SELECT '00001315','1a' UNION all

    SELECT '00001316','456' UNION all

    SELECT '00001315','10b' UNION all

    SELECT '00001316','xyz' UNION all

    SELECT '00001315','1b' UNION all

    SELECT '00001316','123' UNION all

    SELECT '00001316','01a' UNION ALL

    SELECT '00001315','9a'

    ;

    SELECT * FROM #ApplicationBldTypes

    ;

    I used a Temp table as a substitute for your table. Change #ApplicationBldTypes to dbo.ApplicationBldTypes and you should be good to go depending on your indexes...

    --===== This is exactly the same code I wrote before except we're not reading

    -- from a Temp Table and I've added the WHERE clause you wanted.

    WITH

    cteEnumerate AS

    (

    SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY BAForeignKey ORDER BY RowID),

    BAForeignKey,

    BLDClass

    FROM #ApplicationBldTypes

    WHERE BAForeignKey = '00001315' --<<< LOOK HERE! Could be a variable in a proc!!!

    )

    SELECT BAForeignKey,

    BCAClass1 = MAX(CASE WHEN RowNum = 1 THEN BLDClass ELSE '' END),

    BCAClass2 = MAX(CASE WHEN RowNum = 2 THEN BLDClass ELSE '' END),

    BCAClass3 = MAX(CASE WHEN RowNum = 3 THEN BLDClass ELSE '' END),

    BCAClass4 = MAX(CASE WHEN RowNum = 4 THEN BLDClass ELSE '' END),

    BCAClass5 = MAX(CASE WHEN RowNum = 5 THEN BLDClass ELSE '' END)

    FROM cteEnumerate

    GROUP BY BAForeignKey

    -- ORDER BY BAForeignKey -- Don't need this because you're using RBAR.

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi There

    Thanks you so much for your help 🙂

    Just one last question

    Is it possible to save this query as a view?

    Andre

  • Sure... you'll probably need to remove the WHERE clause in the first CTE. You can use such a WHERE clause when you select from the view. Be well advised that if you put any of the 5 calculated columns in an external WHERE clause, the whole view will need to resolve for the entire table before it can make such a decision.

    With that in mind and if you really need the code to return just one row at a time, you might want to consider turning it into a real "Inline Table Valued Function" just to prevent people the temptation of filtering on an aggregated column from the view.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • HI Again

    Thanks for that...not exactly sure I understood what you said...still new to the real power of SQL.

    I have decided to create a stored proceedure and access that from the application.

    Once again Thanks for your help 😀

Viewing 15 posts - 1 through 15 (of 15 total)

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