Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Question Unanswered: Is it possible to dynamically add duplicate rows to a set of data?

    Hey everyone,

    I have something I'm trying to do for a customer but am wondering if it's possible since I'm not sure how to do it.

    I have a list of data in SQL Server that brings up user roles from a database. A few of the roles need to be changed within the SQL code for this one query, which I'm doing with CASE Statements...simple stuff.

    The problem is that the customer wants to duplicate rows that have 2 specific roles, but also change the user role listed to those roles. For instance, I need to change the 'BECO' role to 'ECO', 'MECO' to 'ECO', etc. However, for one role ("BCSO') I need to change it to show 'Comm Sq' and also 'TCO' in two separate rows, with the other data repeating. Not sure why but this is what was requested, since it would be very simple to just put a CASE statement for BCSO and have it show 'Comm Sq/TCO' or something.

    The question I have is if it is even possible to write a query to do this so that one piece of data (the user role in this case) can be changed to dynamically show two separate roles in separate rows while duplicating the other information in the query result?

    Basically it'll look something like this:

    bob@email.com | Comm SQ | information about BCSO
    bob@email.com | TCO | information about BCSO

    Any help would be appreciated!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Add a translation table with the values
    Code:
    RuleOrig  RuleDispl
    BECO      ECO
    MECO      ECO
    BCSO      Comm Sq
    BCSO      TCO
    When you JOIN your table with this table on Rule = RuleOrig and display the RuleDispl column, you are rid of your hard to maintain CASE, you get two records for Rule = 'BCSO', and you can create a maintenance form/window for this table, so your customer has full control over it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    4
    Sweet, thanks a lot! I've never had to do something like this before so I was not familiar with it. I used CASE since it was only a few certain ones but yeah they can always add to it. I'll give that a try and see how it works. Always nice to learn new ways to solve problems. Appreciate it!

  4. #4
    Join Date
    Sep 2010
    Posts
    4
    To add on to this, can you show me the right syntax for coding the join to the translated table just to make sure I'm doing it properly? Obviously my not sleeping well last night is kicking in because I can't think clearly haha.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by easyeman View Post
    To add on to this, can you show me the right syntax for coding the join to the translated table just to make sure I'm doing it properly? Obviously my not sleeping well last night is kicking in because I can't think clearly haha.
    We're taking about a real basic JOIN here, nothing special. Your sleep must definitely have been waaaaay too short
    Code:
    SELECT T1.email, T2****leDispl, T1****leInformation
    FROM YourTable AS T1
        INNER JOIN RuleTranslationTable AS T2 ON
             T1****le = T2****leOrig
    I hope you can activate that brain part that comes up with good Table and Column names, long enough to come up with better names than mine.

    Don't ask me why I cannot write ". Rule" (without a space between "." and "Rule"). The forum software changes it into "****le". Is someone afraid we are trying to smuggle links to Rumanian websites in this forum?? I guess if this makers our lives safer, it's worth the price.
    Last edited by Wim; 09-29-10 at 06:52.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    . ru was censored by Pat due to a very, very high incidence of spam including that about a year ago. Unfortunately, it appears that the rule does not take account of surrounding characters.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2010
    Posts
    4
    Quote Originally Posted by Wim View Post
    We're taking about a real basic JOIN here, nothing special. Your sleep must definitely have been waaaaay too short
    Code:
    SELECT T1.email, T2****leDispl, T1****leInformation
    FROM YourTable AS T1
        INNER JOIN RuleTranslationTable AS T2 ON
             T1****le = T2****leOrig
    I hope you can activate that brain part that comes up with good Table and Column names, long enough to come up with better names than mine.
    ha ok nevermind, I get it. I was overthinking it and was trying to actually do all the translation within the join, like

    Code:
    SELECT T1.Email, T2.RoleDispl, T1.RoleInformation
    FROM MyTable AS T1
        INNER JOIN
    (
    -- BECO = ECO --
    -- BR = Comm Sq --
    ...etc...
    ) AS T2 ON ...etc...
    That's why I was not able to get it - you were saying to create an entire new table that has them all and then join it. Yeah that is simple, but as I said I wasn't thinking well. So I wasn't even thinking of joining a pre-made table but creating the info in the JOIN itself. I'm sure that could be done but it does make more sense to just create a temp table with those values.

    So yeah that's what I was asking. I'm clearer now. Thanks for the help.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by easyeman View Post
    I'm sure that could be done but it does make more sense to just create a temp table with those values.
    I am not talking about a temporary #table, but a permanent one. One that you give access to to your users, so they can adjust and alter the records in it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •