Post reply

Pivot query to use returned value as first row.

  • June 10, 2010 at 12:06 pm

    #225473

    I have a query ( select mydate, partname from salesorder)

    Returns this

    ROW1 = 1/1/2010|parta

    ROW2 = 1/2/2010|

    ROW3 = 1/3/2010|partb

    ROW4 = 1/4/2010|partc

    ROW5 = 1/5/2010|partd

    ROW6 = 1/5/2010|parte

    ROW7 = 1/6/2010|

    ROW8 = 1/7/2010|partf

    ROW9 = 1/7/2010|partg

    ROW10= 1/7/2010|parth

    Trying to get a return of this

    ROW1 = 1/1/2010|1/2/2010|1/3/2010|1/4/2010|1/5/2010|1/6/2010|1/7/2010

    ROW2 = parta|-|partb|partc|partd|-|partf|

    ROW3 = -|-|-|-|parte|-|partg

    ROW4 = -|-|-|-|-|-|parth

    I hope that makes sense..

    Thanks in advance 🙂

  • June 10, 2010 at 12:11 pm

    #1178838

    Do you want to have the result set as one column with separator or do you want one column per date?

    Edit: and why do you need to do it on using SQL Server instead of frontend app?


    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]

  • June 10, 2010 at 12:19 pm

    #1178840

    one column per date.

    Data will be displayed in reporting service. Havent found a way to do it in there yet.

    I have seen pivot query examples that do something similiar, but you have to know the name of the return value and also name the column header, in my case it is the returned date value.

  • June 10, 2010 at 12:37 pm

    #1178849

    I assume you need a dynamic solution to cover "flexible" date ranges.

    In this case I'd recommend you start with reading the "crossTab" article refernced in my signature followed by the "DynamicCrossTab" article (also referenced in my signature).

    You will also need to use ROW_NUMBER() OVER (PARTITION BY myDate) to get the rows numbered.

    I hope that help you to get started. Post back if you get stuck.


    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]

  • June 11, 2010 at 7:10 am

    #1179141

    Thanks for the help... Is there anything i should do when post is complete???

  • June 11, 2010 at 10:21 am

    #1179301

    jerseyeddiem (6/11/2010)

    Thanks for the help... Is there anything i should do when post is complete???

    The best (in terms of most appreciated) you can do is posting your solution. Not only that others may benefit from it. There also might be someone around providing an alternative solution.


    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]

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

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