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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I got it to work. Thank you so much! I had some references backwards.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply