Looking for possible optimization

Transact-SQL
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

2

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?

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

4

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
5

Holy Smokes + Awesome Sauce.

I never would have thought the case could be used to create a column.

Thank you a ton !
Jeff

6

You're welcome. Yeah, that's a handy technique to know.