Forum Replies Created

Viewing 15 posts - 3,901 through 3,915 (of 5,502 total)

  • RE: Complex(?) Query Question

    @Psion:

    Did you notice that your sample data and your expected result don't match?

    You could verify the result if you'd aggregate the three columns. The result should match the number of...


    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]

    March 25, 2010 at 1:23 pm

    #1139972

  • RE: Forum Etiquette for large sample test data

    Please keep in mind that you should not post real data.

    And if you have a query to auto-generate those 4k rows you could just post that query so we can...


    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]

    March 25, 2010 at 12:28 pm

    #1139931

  • RE: Challenging XQUERY With Final Pivot

    Hi Matt,

    I googled and found the following link: http://beyondrelational.com/blogs/jacob/archive/2008/08/21/xquery-lab-23-retrieving-values-and-position-of-elements.aspx

    I applied it to your scenario resulting in the following code.

    But when comparing execution plans for both solutions this new version seems...


    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]

    March 25, 2010 at 11:38 am

    #1139870

  • RE: Pivot Querrie using Dates

    Marv-1058651 (3/25/2010)

    I am trying to write a query so that my results are in a crosstab format.

    I want the sum of sales by employee grouped by the months going across.

    basically...


    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]

    March 25, 2010 at 10:58 am

    #1139818

  • RE: Case Statement Not Working

    You could use one of the following options. The reason your solution didn't work is you've assigned a table column instead of an alias name.

    Side note: I added an ELSE...


    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]

    March 25, 2010 at 10:56 am

    #1139816

  • RE: Assign value to another fieldname using CASE statement

    The Dixie Flatline (3/25/2010)

    It depends on whether or not it depends? :w00t:

    Yes and/or no. :hehe:


    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]

    March 25, 2010 at 10:40 am

    #1139802

  • RE: Assign value to another fieldname using CASE statement

    Paul White NZ (3/24/2010)

    STR has some interesting properties:

  • The numeric parameter is always converted to FLOAT(53) first
  • The output is always (fixed length) CHAR - the default is CHAR(10)

  • The default string output...


    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]

March 25, 2010 at 9:47 am

#1139747

  • RE: Importing XML through SSIS

    Slick84 (3/25/2010)

    ...Lutz,

    In your query like below:

    DECLARE @xml XML

    SET @xml = (SELECT * FROM OPENROWSET(BULK 'c:\filepath\variablefileName', SINGLE_BLOB) AS x)

    Can I somehow use a variable filename? This is because my filename...


    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]

    March 25, 2010 at 9:40 am

    #1139743

  • RE: Challenging XQUERY With Final Pivot

    Hi Matt,

    I'd use XQuery instead of OPENXML in combination with PIVOT and UNPIVOT.

    Something like the following:

    DECLARE @xml XML

    SET @xml='<ADKMessage>

    <c n="totalmb" tn="3" t="1269017107034">

    <v>139390.0</v>

    <v>571133.0</v>

    </c>

    <c n="name" tn="1" t="1269017107034" kc="true">

    <v>C:</v>

    <v>D:</v>

    </c>

    <c n="freemb" tn="3" t="1269017107034">

    <v>57649.14453125</v>

    <v>516178.40625</v>

    </c>

    <c n="usedmb"...


    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]

    March 24, 2010 at 4:09 pm

    #1139287

  • RE: Importing XML through SSIS

    Thanx Wayne for taking over.

    I've been busy in between so I couldn't follow up for a while...

    @Slick: Glad it worked. 😀

    Sometimes it's easier to rely on T-SQL including some new...


    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]

    March 24, 2010 at 3:39 pm

    #1139280

  • RE: Convert Row values to Columns

    If the values for field2 are fixed (0, 10, 20, 30), then you could either use PIVOT (see BOL for details) or DynamicCrossTab (see the related link in my signature).

    If...


    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]

    March 24, 2010 at 1:37 pm

    #1139206

  • RE: Importing XML through SSIS

    Here's a sample code to populate three tables. Let me know if it helps and/or if you need any further assistance.

    DECLARE @xml XML

    SET @xml=(SELECT * FROM OPENROWSET(

    BULK...


    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]

    March 24, 2010 at 1:32 pm

    #1139201

  • RE: Importing XML through SSIS

    Let's see what we can do...

    Do you have to use SSIS or would a T-SQL / XQuery solution work for you, too?

    If the latter, please provide a (valid) XML snippet...


    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]

    March 24, 2010 at 12:49 pm

    #1139179

  • RE: Assign value to another fieldname using CASE statement

    The Dixie Flatline (3/24/2010)

    Did you not even look at my example casting an integer as SQL_VARIANT datatype?

    The main reason why I try to avoid SQL_VARIANT is the need to cast...


    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]

    March 24, 2010 at 12:43 pm

    #1139174

  • RE: Why create statistics on every column of every table in a database.

    Maybe this argument will help, too:

    Within one query, SQL server will only use one index per table. So, if you have the following querySELECT col1, col2, col3, col4

    FROM table

    WHERE col1='something'...


    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]

    March 24, 2010 at 12:19 pm

    #1139153

    • Viewing 15 posts - 3,901 through 3,915 (of 5,502 total)