2uzhan.com
Advertisement
Now Place:2uzhan.com » Is it possible to dynamically add duplicate rows to a set of data?

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

Microsoft SQL Server @ September 27, 2010   Views:0

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:

[email protected] | Comm SQ | information about BCSO
[email protected] | TCO | information about BCSO

Any help would be appreciated!

--------------Solutions-------------

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.

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!

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.

. 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.

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.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.

Tags:
© 2018 2uzhan.com Contact