Adding a parameter with fields as parameter's values

1

Hello SQL expert,

I am trying to make the color fields as values in one parameter called "Color" but not sure
what is the best way to update my basic query

SELECT A.EMPLOYEE_ID
,B.RED
,B.YELLOW
,B.BLUE
,B.GREEN
,B.BLACK
,B.WHITE
FROM EMPLOYEE_TBL A
JOIN COLOR_TBL B
ON A.EMLOYEE_ID = B.EMPLOYEE_ID

And here is the result set when I run above query:

So, I want to create a parameter called "Color" with all these values (RED, YELLOW, BLUE, BLACK, or WHITE) so I can run the report with either RED, YELLOW, BLUE, BLACK, or WHITE condition.

Anyone how do I update my query?

2

Perhaps you should first change the Y/N columns to a single column called Color that you can put in you WHERE clause

3

Hi Gbritton, do you know how?

4

Try:

SELECT 
     E.EMPLOYEE_ID
   ,CASE 
      WHEN C.RED = 'Y' THEN 'Red'
      ELSE
         CASE WHEN C.YELLOW = 'Y' THEN 'Yellow'
         ELSE
            CASE WHEN C.BLUE = 'Y' THEN 'Blue'
            ELSE
               CASE WHEN C.GREEN = 'Y' THEN 'Green'
               ELSE
                  CASE WHEN C.BLACK = 'Y' THEN 'Black'
                  ELSE
                     CASE WHEN C.WHITE = 'Y' THEN 'White'
                     ELSE
                        NULL
                     END
                  END
               END
            END
         END
      END Color
FROM EMPLOYEE_TBL E
JOIN COLOR_TBL C
ON E.EMLOYEE_ID = C.EMPLOYEE_ID;
1 Like
5

Thank you jotorre. Great idea! It works for me!

6

Since some employees have more than one color, I would allow for any/all of them to be listed:

SELECT A.EMPLOYEE_ID
,STUFF(
 CASE WHEN B.RED = 'Y' THEN ',RED' ELSE '' END +
 CASE WHEN B.YELLOW = 'Y' THEN ',YELLOW' ELSE '' END +
 CASE WHEN B.BLUE = 'Y' THEN ',BLUE' ELSE '' END +
 CASE WHEN B.GREEN = 'Y' THEN ',GREEN' ELSE '' END +
 CASE WHEN B.BLACK = 'Y' THEN ',BLACK' ELSE '' END +
 CASE WHEN B.WHITE = 'Y' THEN ',WHITE' ELSE '' END 
 1, 1, '') AS [COLOR(S)]
FROM DBO.EMPLOYEE_TBL A
INNER JOIN DBO.COLOR_TBL B
ON A.EMLOYEE_ID = B.EMPLOYEE_ID
7

I thought that too, but I assumed I had misunderstood the requirements so i kept my trap shut!!

I would be inclined to have a sub-table for Colour with rows containing EMployeeID and ColourCode (or ColourName). No need to add a new column if a new Colour is added to the range in future, and no need to store NO-colours either.

8

Thank you Scott.