www.webdeveloper.com
Results 1 to 2 of 2

Thread: Transposing undefined values from ROWS into COLUMNS

Hybrid View

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Transposing undefined values from ROWS into COLUMNS

    Hi,

    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:


    CLIENT AGE GENDER CHARACTERISTIC
    ---------------------------------------------
    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:

    CLIENT AGE GENDER CHARAC-1 CHARAC-2 CHARAC-3 CHARAC-4 CHARAC-5
    --------------------------------------------------------------------------------------
    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

  2. #2
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    I'm not an MS SQL guy, but this blogger seems to know what to do: http://anthony-yio.blogspot.com/2007...oupconcat.html

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles