loiter
June 19, 2019, 3:06pm
#1
Hello,
I am looking for a faster way to build columns based on a common value. For example, I have extracts that need to have a separate column for each ethnicity. So, a column for White and a value of 'y' or 'n', a column for Asian, same value. Currently, I am building CTE for each ethnicity type and then doing a lot of joins.
Can this be optimized?
thanks
J
There should be a better way than joining separate CTEs. Probably a simple cross-tab query.
Can you provide more detail about your specific table structure and what query results you want to see?
loiter
June 19, 2019, 3:19pm
#3
This is for loading pre-id for state tests. Data is coming for a demographic table:
FirstName, LastName, ID, Ethnicity
Mike, Jones, 123, White
Tom, Jones 234, Asian
FirstName, LastName, ID, White, Asian, Multi, Hispantic
Mike, Jones, 123, Y, N, N, N
Tom, Jones, 234, N, Y, N, N
Thanks
J
If you want to see totals of varying ethnicities in one query, you can do something like this:
SELECT SUM(CASE WHEN White = 'Y' THEN 1 ELSE 0 END) AS White_Total,
SUM(CASE WHEN Asian = 'Y' THEN 1 ELSE 0 END) AS Asian_Total,
....
1 Like
loiter
June 19, 2019, 4:45pm
#5
Holy Smokes + Awesome Sauce.
I never would have thought the case could be used to create a column.
Thank you a ton !
Jeff
You're welcome. Yeah, that's a handy technique to know.