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.
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;
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
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.