if else in sql function

Question

I have a table something similar to this:(THE FIRST ROW ARE HEADERS)

ID --peach --cherry --plum --berry --apple --mango --orange --grapes

932 --peach --NULL --NULL--NULL --NULL --NULL --NULL --NULL

932 --NULL --NULL --NULL --berry--NULL --mango --NULL --NULL

932 --NULL --NULL -- NULL--berry --NULL --mango --NULL --NULL

926 --NULL --cherry --NULL--NULL --apple --NULL --NULL --NULL

926 --NULL --cherry --NULL--NULL --apple --NULL --NULL --grapes

I am trying to create a function that would get the ID and pick the initial from every other column if present and then concatenate them like below

ID fruits

932 P

932 B,M

932 B,M

932 C,A

932 C,A,G

whats the best to do this and create a function which i can call later to create a final table. Any help is appreciated. The table has about 51,000 rows.

Answer 1

This should do it:

SAMPLE DATA:

CREATE TABLE #temp1(ID     INT
           , peach  VARCHAR(10)
           , cherry VARCHAR(10)
           , plum   VARCHAR(10)
           , berry  VARCHAR(10)
           , apple  VARCHAR(10)
           , mango  VARCHAR(10)
           , orange VARCHAR(10)
           , grapes VARCHAR(10));


INSERT INTO #temp1
VALUES
(932,'peach' , NULL ,  NULL ,  NULL ,   NULL ,  NULL ,  NULL,  NULL  ),
(932,NULL  , NULL   ,NULL   ,'berry'  , NULL   ,'mango'  ,NULL  ,NULL  ),
(932,NULL  , NULL   ,NULL   ,'berry'  , NULL   ,'mango'  ,NULL  ,NULL  ),
(926,NULL  , 'cherry' ,NULL   ,NULL   , 'apple'  ,NULL   ,NULL  ,NULL  ),
(926,NULL  , 'cherry' ,NULL   ,NULL   , 'apple'  ,NULL   ,NULL  ,'grapes')

QUERY:

SELECT #temp1.ID
    , ISNULL(SUBSTRING(#temp1.peach,1,1),'')
    + ISNULL(SUBSTRING(#temp1.cherry,1,1),'')
    + ISNULL(SUBSTRING(#temp1.plum,1,1),'')
    + ISNULL(SUBSTRING(#temp1.berry,1,1),'')
    + ISNULL(SUBSTRING(#temp1.apple,1,1),'')
    + ISNULL(SUBSTRING(#temp1.mango,1,1),'')
    + ISNULL(SUBSTRING(#temp1.orange,1,1),'')
    + ISNULL(SUBSTRING(#temp1.grapes,1,1),'')
FROM   #temp1;

RESULT: