Post reply

Using CASE to

  • October 19, 2006 at 8:31 am

    #192727

    Hi all,

    Another SQL question for you.

    I have a table of columns that basically contain 1 where the column is applicable. E.g.

    Pupil_ID    Desc1     Desc2      Desc3     Desc4     Desc5
    111
    211
    31
    41111
    51

    I was then going to have a column on the end that I could combine everything to use in other queries like

    SELECT   *, 'SERVICES' =

          CASE 

          WHEN Desc1 IS not NULL THEN 'Desc1'

          WHEN Desc2 IS not NULL THEN 'Desc2'

          WHEN Desc3 IS not NULL THEN 'Desc3'

          WHEN Desc4 IS not NULL THEN 'Desc4'

          WHEN Desc5 IS not NULL THEN 'Desc5'

          ELSE 'No Service'

          END

    FROM #Service_Matrix

    However this isnt working because if I have more than one service it only brings back the 1st on in the list. I need to be able have the column concatenate the services like 'Desc1, Desc3'

     

    Am I using totally the wrong function or is there any other SQL I can add that could sort it out.

    Any help would be really appreciated.

    Thanks

    Debbie

  • October 19, 2006 at 12:22 pm

    #666907

    The reason you are having trouble with the query is because of the table design.  I recommend you create three tables.  One for Pupil, one for Service and a M:M table.

    Pupil (pupil_id int)

    Service (service_id int, service_nm varchar(10))

    PupilService (pupil_id int, service_id int)

    This would make queries the associative table much easier than the case/if logic you have above.

    However.....if you are stuck with the table the way it is, you will need to do something like this:

    create

    table #matrix (pupil_id int, services varchar(100))

    insert

    into #matrix select pupil_id, '' from ServiceMatrix

    update

    #matrix set services = services + ' desc1' from ServiceMatrix where #matrix.pupil_id = ServiceMatrix.pupil_id and ServiceMatrix.desc1 is not null

    update

    #matrix set services = services + ' desc2' from ServiceMatrix where #matrix.pupil_id = ServiceMatrix.pupil_id and ServiceMatrix.desc2 is not null

    update

    #matrix set services = services + ' desc3' from ServiceMatrix where #matrix.pupil_id = ServiceMatrix.pupil_id and ServiceMatrix.desc3 is not null

    update

    #matrix set services = services + ' desc4' from ServiceMatrix where #matrix.pupil_id = ServiceMatrix.pupil_id and ServiceMatrix.desc4 is not null

    update

    #matrix set services = services + ' desc4' from ServiceMatrix where #matrix.pupil_id = ServiceMatrix.pupil_id and ServiceMatrix.desc5 is not null

    select

    * from #matrix

  • October 20, 2006 at 4:00 am

    #667005

    Excellent.

    I went with the second option and Its working exactly how I needed it too. Its so easy when you know how

    Thanks again

    Debbie

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply