Forum Replies Created

Viewing 15 posts - 43,726 through 43,740 (of 59,097 total)

  • RE: Variable columns to be returned

    Like this...

    [font="Courier New"]--===== Create a test table.  This is NOT part of the solution

    DECLARE @T TABLE 

            (

            Name VARCHAR(100), 

            StartDate DATETIME, 

            Data1 BIT, 

            Data2 BIT, 

            Data3 BIT, 

            Data4 BIT, 

            Data5 BIT

            )

     

    --===== Populate the test table with data.

         -- Again, this is not part of the solution.

     INSERT INTO @T

     SELECT 'Mike' , '05/21/09', '1', '0', '0', '1', '0' UNION ALL

     SELECT 'Bruce', '05/21/08', '0', '1', '1', '0', '0' UNION ALL

     SELECT 'John' , '09/09/09', '0', '0', '0', '0', '1'

    --===== Use STUFF to kill the leading comma on the concatenation

     SELECT Name,

            StartDate,

            STUFF(CASE Data1 WHEN 1 THEN ',Blue'   ELSE '' END

                + CASE Data2 WHEN 1 THEN ',Green'  ELSE '' END

                + CASE Data3 WHEN 1 THEN ',Yellow' ELSE '' END

                + CASE Data4 WHEN 1 THEN ',Black'  ELSE '' END

                + CASE Data5 WHEN 1 THEN ',Violet' ELSE '' END

            ,1,1,'') AS Colors

       FROM @T

    [/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 7:51 pm

    #998645

  • RE: Variable columns to be returned

    Florian Reischl (5/21/2009)

    If the trailing comma is no problem you can remove the @Results table and direct return the data.

    Or, use a leading comma and STUFF it just like you...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 7:31 pm

    #998641

  • RE: Check if a value is greater than from a column

    I wouldn't include any of the CASE statements in this query. Calculate all of the common equations in a CTE and then SELECT from that using the column aliases...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 7:09 pm

    #998639

  • RE: Switching to MySQL or SQL Server Express: What is best for an ISV?

    Josh Turley (5/21/2009)

    We have customers using SQL Server Standard/Enterprise, so it'd be easier to teach tech support how to manage one type of RDMBS instead of 2 or 3.

    I...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 7:02 pm

    #998638

  • RE: Rearranging data

    Actually, because all of the row data is to be stored as CSV's in a single column, it's a concatenation job. Please see the following article for how to...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 6:58 pm

    #998637

  • RE: How to trim the decimal places?

    Bruce W Cassidy (5/20/2009)

    Jeff Moden (5/20/2009)

    Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online....

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 21, 2009 at 9:08 am

    #998329

  • RE: How to trim the decimal places?

    Bruce W Cassidy (5/20/2009)

    [font="Verdana"]I guess the issue is with not wanting to round the decimal places. Sadly, even using the various formatting options in SQL Server Reporting Services will...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 20, 2009 at 3:20 pm

    #997941

  • RE: Eliminating Cursors

    Charles Kincaid (5/20/2009)

    Kevin Rathgeber (5/20/2009)

    Charles Kincaid (5/20/2009)

    If I remember correctly is this not how sequence generators in Oracle work?

    Is that word allowed on this forum 😀

    If we don't mention them...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 20, 2009 at 3:16 pm

    #997940

  • RE: Are the posted questions getting worse?

    I think all kids should leave home just as soon as they think they know it all. Should save 10-12 years on upbringing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 20, 2009 at 3:14 pm

    #997937

  • RE: Are the posted questions getting worse?

    Roy Ernest (5/20/2009)

    All of you need a vacation... You have all gone crazy.... 😛

    Heh... SHHHH!!! It's fun to watch. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 20, 2009 at 3:12 pm

    #997933

  • RE: Are the posted questions getting worse?

    Alvin Ramard (5/20/2009)

    Lynn Pettis (5/20/2009)

    You know, there are a few people who just need to walk away from the computer and get a job flipping burgers or something.

    Got it!

    Oh, what's...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 20, 2009 at 3:06 pm

    #997923

  • RE: Eliminating Cursors

    Kevin Rathgeber (5/19/2009)

    Hi Jeff.....I just read your solution regarding the sequence tables. Very good explanation. I have one minor concern (very minor). You have indicated having the...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 19, 2009 at 8:09 pm

    #997348

  • RE: T-sql procedure error in job

    The errors in the job output in the GUI are truncated and you're probably not seeing the whole thing and that's why it looks different. Notice that it didn't...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 19, 2009 at 7:09 pm

    #997343

  • RE: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

    Very cool. Glad you could use so much of the article. Thanks for the feedback, Ray.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 19, 2009 at 6:25 pm

    #997336

  • RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

    fun_sunshine_summer (5/19/2009)

    I have a problem where a value in one row of the record set is base on the value of the previous row. I hate doing RBAR so I...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    May 19, 2009 at 3:16 pm

    #997268

Viewing 15 posts - 43,726 through 43,740 (of 59,097 total)