Populate Column Based on Distinct Values
-
Hello!
I am trying to populate a column based on the number of distinct values in a different column. If there is only one distinct department, Export Department should be populated with *, if there is more than one distinct department, Export Department should be populated with the same value in Department. Any help would be appreciated.
Thank you!
-
I'm not really sure what you're trying to do, but it sounds like something like this:
UPDATE tn
SET tn.[Export Deparment] = CASE WHEN Distinct_Department_Count = 1 THEN '*' ELSE tn.Department END
FROM dbo.table_name tn
CROSS JOIN (
SELECT COUNT(DISTINCT Department) AS Distinct_Department_Count
FROM dbo.table_name
) AS Distinct_Department_CountsSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Why is only one of the rows updated to include an asterisk in your example? What is special about that row?
If there was a row of data for department 0002 in your example, would all of the 0001 rows need to be updated to contain 0001 in Export Department?
-
Sorry for the confusion. Yes, as the data stands right now the entire column should be populated with an *. If the department column included 0001 and 0002, then Export Department would be a copy of department.
- Kin16 wrote:
Sorry for the confusion. Yes, as the data stands right now the entire column should be populated with an *. If the department column included 0001 and 0002, then Export Department would be a copy of department.
If you were to post readily consumable data as a part of the question, that would eliminate a lot of "confusion". Please see the article at the first link in my signature line below for one way to do that.
--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) - Kin16 wrote:
Sorry for the confusion. Yes, as the data stands right now the entire column should be populated with an *. If the department column included 0001 and 0002, then Export Department would be a copy of department.
I wrote my code above assuming that all departments would get * if a single value.
Does my code not do what you wanted to do here?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thank you for your code. I tried it but I was am getting an error with Distinct_Department_Count. I am getting an invalid table name error.
- Kin16 wrote:
Thank you for your code. I tried it but I was am getting an error with Distinct_Department_Count. I am getting an invalid table name error.
Did you replace dbo.table_name with your own table's schema/name?
-
I got it to work. Thank you so much! I had some references backwards.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply