Post reply

display all the column values in rows

  • July 13, 2009 at 8:59 am

    #134253

    Hi

    I want to display the display all the column values in rows

    imp Note : not all the columns need not refer to single datatype

    eg.,

    Let us suppose that we have a table like below

    col1 | col2 | col3

    --------*---------*----------

    Value 1 | Value 2 | Value 3

    And change it to one that looks like this:

    Name | Value

    -----*---------

    col1 | Value 1

    -----*---------

    col2 | Value 2

    -----*---------

    col3 | Value 3

  • July 13, 2009 at 9:39 am

    #1023979

    Look at PIVOT or a cross tab report.

    It's not an easy thing to do, especially as what do you do with multiple rows? Are they extended to the right?

  • July 13, 2009 at 11:38 am

    #1024069

    It's rather a task for UNPIVOT...

    Here's a sample (pretty much straight off BOL and slightly modified):

    DECLARE @t TABLE (col1 VARCHAR (10), col2 VARCHAR (10), col3 VARCHAR (10))

    INSERT INTO @t VALUES ('Value 1', 'Value 2', 'Value 3')

    SELECT * FROM @t

    SELECT Name, Value

    FROM

    (SELECT col1, col2, col3

    FROM @t pvt) p

    UNPIVOT

    (Value FOR Name IN

    (col1, col2, col3)

    )AS unpvt

    /* result set:

    NameValue

    col1Value 1

    col2Value 2

    col3Value 3

    */

    Note: I'm sure Jeff's going to show up presenting a "PIVOT-phrase-free" solution but that's all I could come up with as a quick fix...

    Edit: spelling fixed


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 13, 2009 at 11:30 pm

    #1024246

    Hi Steve,

    I want to display the values as I have given in the example

    But, all columns do not refer to a same data type

    Thanks

  • July 14, 2009 at 1:22 am

    #1024260

    Hi,

    Just cast\convert all the cols to same data type

    DECLARE @t TABLE (col1 VARCHAR (10), col2 int, col3 CHAR (10))

    INSERT INTO @t VALUES ('Value 1', 2, 'Value 3')

    SELECT * FROM @t

    SELECT Name, Value

    FROM

    (SELECT col1, cast(col2 as varchar(10)) as col2, cast(col3 as varchar(10)) as col3

    FROM @t pvt) p

    UNPIVOT

    (Value FOR Name IN

    (col1, col2, col3)

    )AS unpvt

    /* result set:

    Name Value

    col1 Value 1

    col2 2

    col3 Value 3

    */

  • July 14, 2009 at 8:27 am

    #1024427

    pmadhavapeddi22 (7/13/2009)

    Hi Steve,

    I want to display the values as I have given in the example

    But, all columns do not refer to a same data type

    Thanks

    This is just another good example of how important sample data are that actually do show your current scenario....

    Kupy made the required adjustment (assuming, that varchar(10) is the type that would match your data...).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 15, 2009 at 1:43 am

    #1024852

    thank you to all for the responses

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

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