Post reply

View the SQL query out-put in horizontal format.

  • January 10, 2005 at 7:09 pm

    #60379

    Hi all,

     

    I have the following SQL query that I like to view the out put in horizontal format:

     

    Select * from ABC

     

    Instead of getting out-put like

     

    Id         F_name            L_name

    1          Jim                    Smith

    2          Tom                  Jones

     

    I like to see the out-put like:

     

    Id                     1          2

    F_name            Jim      TOM

    L_name            Smith    Jones

     

    Please advice.

     

    Thanks in advance,

     

     

    Abrahim

  • January 10, 2005 at 8:24 pm

    #536743

    Your example doesnt make sense.  It lists Jim and Smith as firstnames?

    You can pivot a table by using case statements or selfjoins, if that is what you are trying to do.

    Regards

    Al

  • January 10, 2005 at 8:40 pm

    #536744

     

    Oops, Sorry for miss typing:It lists Jim and Tom as First_name and Smith and Jones as Last_name.

     

    Select ID, First_Name, Llast_name from ABC

    ID                  1       2

    First_name      Jim   Tom

    Last Name      Smith    Jones

     

    Do you have any example for case statements or selfjoins for the above SQL querry?

     

    Thanks,

     

    Abrahim

  • January 10, 2005 at 10:01 pm

    #536748

    Hello Abrahim,

    This query has got to be for presentation (i.e. a report or a web screen), yes? If so, what's the target presentation app?   I'd try to do it there, in the presentation language.

    For example, I have a routine in Excel that rotates rows and columns, and I know a kluge to do it in HTML.  But straight SQL would be my last choice for doing this task. I'll watch this post carefully to see if someone has a good solution for you.

    Let me know if you want the Excel macro, I'd be glad to send it to you. I usually open the data I need with Access (benefit: it keeps the column names when you copy rows from a table), copy the rows I want into Excel, and then rotate rows and columns. I use this format for writing specs and for diagnosis.

    Regards,

    Bob Monahon

  • January 10, 2005 at 10:07 pm

    #536749

    Hi Bob,

    How about using crosstab query?

    Abrahim

  • January 11, 2005 at 5:07 am

    #536780

    There are some critical differances between crosstab and rotate:

    A. Rows: Crosstab creates 1 row of output for 1 or more rows of input. 

    Rotate creates 1 row of output for each column of data.

    B. Columns: Crosstab creates multiple columns out of a single column, based on a value common to all rows being grouped together.  Rotate creates one column for each row in the input.

    I'll let the back of my mind work on it, but nothing is jumping out at me.

    Regards,

    Bob Monahon

  • January 11, 2005 at 6:25 am

    #536795

    First of all, I suspect that Bob has the correct solution, that being to deal with presentation in the presentation layer. If you really need the rows inverted you could examine the following links for an option or two. Solutions for this are generally very specific to the situation.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=132980

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=133646

    I believe there was a better thread out there but I couldn't find it.

  • January 11, 2005 at 2:17 pm

    #536906

    Thank you all for the help.

    Abrahim

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

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