I have been searching for a while and couldn't find any examples/solutions to my problem. I am querying an MS SQL Server database and collecting clients with certain characteristics. I want to have each distinct client as row and all characteristics as columns. Up to here, this is just a simple "flat" query. The problem comes in as one of the client characteristics may have different (1 or more than one) values, and I want them all. Right now my query is displaying each one of these different values for that characteristic as a new instance and thus my clients (rows) aren't distinct. I may have one or more instances for the same client because of the possible different values of that characteristic. In addition, the values for this characteristic could be multiple and more than 100. I am emphasizing this because I saw some examples out there that using a CASE statement create new columns for each different value. However, those values here can't be predetermined into a CASE comparion because they are not known and are many (the are exemplified below with letters). Also, while clients can have more one value for this characteristic, they usually don't have more than 5 different values. So I was thinking of creating 5 new columns and assigning every different value to each one of the columns.

Right now the output looks like this:

1 40 M A
1 40 M B
1 40 M D
1 40 M R
1 40 M F
2 23 M R
2 23 M F
3 78 F D
3 78 F N
3 78 F L
4 66 F K
5 11 M J
5 11 M P
5 11 M I
5 11 M O
6 83 F W
6 83 F F
6 83 F B

I want to have something looking like this:

1 40 M A B D R F
2 23 M R F
3 78 F D N L
4 66 F K
5 11 M J P I O
6 83 F W F B

As you can see, the instances are still distinct and the columns were added.

Anyone knows what would be the best approach to this problem?

-- The server doesn't have the PIVOT function, and is ok to leave blank cells when clients don't have all 5 different values.

Thanks in advance,

- Guido