locked
Please suggest some query. RRS feed

  • Question

  • Hi,

    please suggest some query for the following data

     

    col1                                             col2

    ZA00000359611290030                    abl
    24061801032292761                        abl
    4006006253361227                          abl
    2419160913460386                          abl

    ZA00000359611290030                     acl
    24061801032292761                         acl
    4006006253361227                           acl
    2419160913460386                           acl

    ZA00000359611290030                      arl
    24061801032292761                          arl
    4006006253361227                            arl
    2419160913460386                            arl

     

     

    need to get output like  

     

    col1                                  col2                            col3                                col4

    ZA00000359611290030         abl                               acl                                 arl
    24061801032292761             abl                               acl                                 arl
    4006006253361227               abl                               acl                                 arl
    2419160913460386               abl                               acl                                 arl

     

     

    thanks..

    Friday, September 23, 2011 1:57 PM

Answers

  • Try

    select * from (select *, 'Col'+ ltrim(1+dense_rank() over (partition by Col1 order by Col2)) as Rn from myTable) src PIVOT 
    (min(Col2) FOR Rn IN ([Col2],[Col3],[Col4])) pvt
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kent Waldrop Friday, September 23, 2011 2:09 PM
    • Marked as answer by Kalman Toth Wednesday, September 28, 2011 10:26 AM
    Friday, September 23, 2011 2:04 PM

All replies

  • Try

    select * from (select *, 'Col'+ ltrim(1+dense_rank() over (partition by Col1 order by Col2)) as Rn from myTable) src PIVOT 
    (min(Col2) FOR Rn IN ([Col2],[Col3],[Col4])) pvt
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kent Waldrop Friday, September 23, 2011 2:09 PM
    • Marked as answer by Kalman Toth Wednesday, September 28, 2011 10:26 AM
    Friday, September 23, 2011 2:04 PM
  • That is an ugly, unstable pivot.  It looks to me like you need to employ (1) the row_number() analytic function to get sequence numbers for the col1 data and then (2) a pivot to translate the rows into columns.

    The rub here is that you have provided no logical order to your data.  Yeah, I know, I know, you are going to say you want the data to be colimiated based on the order it appears in the table.  You need to understand that this is a bit of baloney.  Tables are by definition un-ordered.  What that means is that the order that data will colimiate will be unstable because there is no guarantee that the table will come out in a specific order unless you have a specific ORDER BY clause that can be applied.  Hang on and I will get you the corresponding BS example.

    EDIT:

    Even better, use Naomi's example; it looks like she has you covered.  ( And thank you for picking this up, Naomi. )



    Friday, September 23, 2011 2:05 PM
  • Below is the query. TRy this one.

    SELECT col1,
    MAX(CASE WHEN col2= 'abl' THEN col2 END) AS  col2,
    MAX(CASE WHEN col2 = 'acl' THEN col2 END) AS col3,
    MAX(CASE WHEN col2 = 'arl' THEN col2 END) AS col4,
    FROM sample
    GROUP BY col1

    I dint try this. straight through my head.


    Sandeep Dasam

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, September 23, 2011 2:07 PM
  • Kent,

    Requirement it is like that ....i cann't do any thing with data.....

    Friday, September 23, 2011 2:41 PM
  • You can also use SSRS with built-in dynamic PIVOT. Easy to use!

    SSRS videos: http://www.youtube.com/results?search_query=sqlusa+report


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, September 28, 2011 10:29 AM