How to populate values in a cell from different table, separated by comma

Question

I have six tables

a. Employee

EmployeeId         EmployeeName
123                John
125                Peter
129                Jack

b. EmployeeParameterValue

EmployeeParameterValueId         EmployeeId    ParameterValueId
1                                123           1
2                                125           2
3                                129           3

c. ParameterValue

ParameterValueId    ParameterId    Value
1                   2              1, 2, 3
2                   3              1, 2
3                   2              3

d. Parameter

ParameterId      Name
2                WorkedStates
3                WorkedType

e. WorkedStates

WorkedStatesId    WorkedStatesName
1                 CA
2                 WA
3                 NY

f. WorkedType

WorkedTypeId          WorkedTypeName
1                     Hourly
2                     Salaried

I need to write a report in following format:

EmployeeId         EmployeeName       Parameter          ParameterValue
123                John               WorkedStates       CA, WA, NY
125                Peter              WorkedType         Hourly, Salaried
129                Jack               WorkedStates       NY

I am able to write a query which fetches first 3 columns. But I am not able to write a query which fetches the data in the fourth column.

Please advise.

A column with a comma-separated list of values is a poor database design decision. It leads to all kinds of problems, like the kind you are experiencing. You should consider normalizing such tables to first normal form. — Honeyboy Wilson, Sep 10 '18 at 18:20
but you only need do split that comma separated column and you are good to go, do you have any simple split function? — Dejan Dozet, Sep 10 '18 at 18:23
Have you tried anything so far? Then are plenty of examples on this very website on how to split delimited data or turn it into it on SQL Server. — Thom A, Sep 10 '18 at 18:25

score 1 · Answer 1 · answered Sep 10 '18 at 20:00

Clearly the design could be improved.

Now, you could split the delimited string, join to the appropriate table, and then re-aggregate the results, or you can consider a UDF

Example

Select [dbo].[ParameterValue](2,'1, 2, 3')

Returns

CA, WA, NY

The UDF if interested

CREATE FUNCTION [dbo].[ParameterValue] (@P int,@S varchar(max))
Returns varchar(max)
AS
Begin

    Select @S = replace(@S,MapFrom,MapTo)
     From  (Select Top 10000 
                   MapFrom = WorkedStatesId
                  ,MapTo   = WorkedStatesName
             From  WorkedStates 
             Where @P=2
             Order By 1 desc
            Union All
            Select Top 10000
                   MapFrom = WorkedTypeId
                  ,MapTo   = WorkedTypeName
             From  WorkedType 
             Where @P=3
             Order By 1 desc
            ) A

    Return @S

End
-- Syntax : Select [dbo].[ParameterValue](2,'1, 2, 3')

I would not suggest this on a large table, but the performance may not be so bad.

score 0 · Answer 2 · answered Sep 10 '18 at 18:25

0

Use JSON save your data with json format. If you save data with json format you have a object into any col of table that help you better organized your data in tables. Save two or tree or more table fields in one json and save this json into one col of table.

answered Sep 10 '18 at 18:25

Hossein Shafiei

111
1
3

Save JSON as a text value :-) – Hossein Shafiei Sep 10 '18 at 18:34
2

Is this even an answer? How does it solve the problem? – Salman A Sep 10 '18 at 18:38

How to populate values in a cell from different table, separated by comma

2 Answers2