Post reply

doubt

  • July 18, 2008 at 11:09 pm

    #188309

    i have the following table

    remarktyperemarks

    Remarktype1Remarktype11

    Remarktype1Remarktype12

    Remarktype1Remarktype13

    Remarktype1Remarktype14

    Remarktype1Remarktype15

    Remarktype2Remarktype21

    Remarktype2Remarktype22

    Remarktype2Remarktype23

    Remarktype2Remarktype24

    Remarktype2Remarktype25

    Remarktype3Remarktype31

    Remarktype3Remarktype32

    Remarktype3Remarktype33

    Remarktype3Remarktype34

    Remarktype3Remarktype35

    i want to sql query wgich can give the result as

    Remarktype1Remarktype2Remarktype3

    Remarktype11Remarktype21Remarktype31

    Remarktype12Remarktype22Remarktype32

    Remarktype13Remarktype23Remarktype33

    Remarktype14Remarktype24Remarktype34

    Remarktype15Remarktype25Remarktype35

    can u pls suggest any way to do it?

    thanx in advance

  • July 18, 2008 at 11:45 pm

    #845317

    Here is an article that explains how to perform a dynamic pivot.

    http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

  • July 18, 2008 at 11:56 pm

    #845318

    i need this for text columns not for aggregates

    thanx for the reply

  • July 19, 2008 at 5:44 pm

    #845361

    jayrsangani (7/18/2008)

    i need this for text columns not for aggregates

    thanx for the reply

    Heh... trust me... with a little help from ROW_NUMBER(), aggregates are the way to go for this...

    --===== Create and populate a test table. This is NOT part of the solution.

    -- This is how we like to see example data in posts.

    -- Please see the link in my signature line below for more info.

    DECLARE @yourtable TABLE (RemarkType VARCHAR(15), Remarks VARCHAR(15))

    INSERT INTO @yourtable

    (RemarkType,Remarks)

    SELECT 'Remarktype1','Remarktype11' UNION ALL

    SELECT 'Remarktype1','Remarktype12' UNION ALL

    SELECT 'Remarktype1','Remarktype13' UNION ALL

    SELECT 'Remarktype1','Remarktype14' UNION ALL

    SELECT 'Remarktype1','Remarktype15' UNION ALL

    SELECT 'Remarktype2','Remarktype21' UNION ALL

    SELECT 'Remarktype2','Remarktype22' UNION ALL

    SELECT 'Remarktype2','Remarktype23' UNION ALL

    SELECT 'Remarktype2','Remarktype24' UNION ALL

    SELECT 'Remarktype2','Remarktype25' UNION ALL

    SELECT 'Remarktype3','Remarktype31' UNION ALL

    SELECT 'Remarktype3','Remarktype32' UNION ALL

    SELECT 'Remarktype3','Remarktype33' UNION ALL

    SELECT 'Remarktype3','Remarktype34' UNION ALL

    SELECT 'Remarktype3','Remarktype35'

    ;WITH

    cteSortedData AS

    (--==== Determin the row numbers for each RemarkType

    SELECT (ROW_NUMBER() OVER (PARTITION BY RemarkType ORDER BY Remarks)) AS RowNum,

    RemarkType,

    Remarks

    FROM @yourtable

    )--==== Pivot the data using the MAX aggregation.

    SELECT MAX(CASE WHEN RemarkType = 'RemarkType1' THEN Remarks ELSE ' ' END) AS RemarkType1,

    MAX(CASE WHEN RemarkType = 'RemarkType2' THEN Remarks ELSE ' ' END) AS RemarkType2,

    MAX(CASE WHEN RemarkType = 'RemarkType3' THEN Remarks ELSE ' ' END) AS RemarkType3

    FROM cteSortedData

    GROUP BY RowNum

    --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)

  • July 21, 2008 at 4:37 am

    #845651

    Jeff Moden (7/19/2008)

    --==== Pivot the data using the MAX aggregation.

    SELECT MAX(CASE WHEN RemarkType = 'RemarkType1' THEN Remarks ELSE ' ' END) AS RemarkType1,

    MAX(CASE WHEN RemarkType = 'RemarkType2' THEN Remarks ELSE ' ' END) AS RemarkType2,

    MAX(CASE WHEN RemarkType = 'RemarkType3' THEN Remarks ELSE ' ' END) AS RemarkType3

    FROM cteSortedData

    GROUP BY RowNum

    You can alternatively use the PIVOT operator to do the pivoting (since this is SQL 2005)...

    SELECT RemarkType1, RemarkType2, RemarkType3

    FROM cteSortedData

    PIVOT (MAX(Remarks) FOR RemarkType IN (RemarkType1, RemarkType2, RemarkType3)) as p

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 21, 2008 at 5:43 am

    #845691

    Yes, you can... you'll loose performance, as well. 😉 It turns out that Cross Tabs are generally quicker than Pivots especially if you preaggregate the data in a derived table. Dunno when the article will come out, but I submitted an article on Cross Tabs and Pivots with a whole bunch of test code that shows this and other neat things about Cross Tabs... I don't know why Microsoft even bothered making Pivots.

    --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)

  • July 21, 2008 at 6:22 am

    #845713

    Jeff Moden (7/21/2008)

    Yes, you can... you'll loose performance, as well. 😉 It turns out that Cross Tabs are generally quicker than Pivots especially if you preaggregate the data in a derived table. Dunno when the article will come out, but I submitted an article on Cross Tabs and Pivots with a whole bunch of test code that shows this and other neat things about Cross Tabs... I don't know why Microsoft even bothered making Pivots.

    That's interesting. I must admit that most of the time I can't remember the pivot syntax and often use the 'old' way myself anyhow.

    I'll keep a lookout for the article 🙂

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 21, 2008 at 7:29 am

    #845752

    Heh... yeah... I'm the same way... it's easier for me to remember an aggregate qualified by a CASE than it is to remember the Pivot syntax because it's so simple. The other neat thing about it is that the Cross Tab syntax migrates from 2k to 2k5 with no changes required and no hit on performance. If it did cost a hit on performance, I'd be all over Pivot like white on rice. 🙂

    Thanks, for the feedback, Ryan.

    --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)

  • July 21, 2008 at 10:30 am

    #845872

    I've found Unpivot useful for normalizing data out of spreadsheets and such. Pivot, so far as I can tell, has negative value.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 9 posts - 1 through 8 (of 8 total)

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