Post reply

Data format -cross tab

  • April 28, 2011 at 1:02 pm

    #236626

    I have the following data.

    name number addr1 addr2 city state zipcountry timezone

    a1 add1 add2 city1st1 zip1usa est

    b2 add2 city2st2 zip2usa est

    c3 add3 city3st3 zip3usa

    I need to convert the above data to the following format.

    name coulumn value

    aaddr1 add1

    aaddr2add1

    acitycity1

    astatest1

    azipzip1

    acountryusa

    atimezoneest

    baddr1 add2

    bcitycity2

    bstatest2

    bzipzip2

    bcountryusa

    btimezoneest

    caddr1 add3

    ccitycity3

    cstatest32

    czipzip3

    ccountryusa

    can we do this with cross tab report?

    Thanks.

  • April 28, 2011 at 1:18 pm

    #1317497

    Have a look at the UNPIVOT syntax.


    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]

  • April 28, 2011 at 1:48 pm

    #1317521

    Thanks for your help.It works fine. But I need to by pass the null values.

    Say like if addr2 has a null values it should not be added in the result set.

    name number addr1 addr2 city state zip country timezone

    a 1 add1 add2 city1 st1 zip1 usa est

    b 2 add2 city2 st2 zip2 usa est

    c 3 add3 city3 st3 zip3 usa

    in this data for the third rwo timezone value is blank. In that case timezone should not be added in the result set.

    Thanks.

  • April 28, 2011 at 2:04 pm

    #1317542

    sql_2005_fan (4/28/2011)

    Thanks for your help.It works fine. But I need to by pass the null values.

    Say like if addr2 has a null values it should not be added in the result set.

    name number addr1 addr2 city state zip country timezone

    a 1 add1 add2 city1 st1 zip1 usa est

    b 2 add2 city2 st2 zip2 usa est

    c 3 add3 city3 st3 zip3 usa

    in this data for the third rwo timezone value is blank. In that case timezone should not be added in the result set.

    Thanks.

    Then you make an outer query that says

    SELECT name, column, value

    FROM (Subquery where you use the unpivot and name these columns) X

    WHERE value IS NOT NULL

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • April 28, 2011 at 3:07 pm

    #1317595

    I tried it .. but its taking more time.. since I am working with 200k records.. I am wondering if there is a way that I can elimante nulls with no outer query.

    Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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