CODEWITHSUNDEEP
sqlsql-server
Ethan Schofer
Ethan Schofer

Reputation: 1848

Concatenate many columns to one comma separate string and insert into new table

Lots of concatenation posts here but can't seem to find the solution to this:

I have a table with 20 columns:

ID Col 1 Col 2 Col 3 Col 4 etc....

I need to concatenate all columns together so that I have two columns:

ID MasterCommaDelimColumn

Then I need to insert this into a new table. The problem seems to be the commas. I can concatenate everything together, but when I add the commas, it sees them as separate columns, and the insert fails.

EDIT:

DECLARE @SQL varchar(MAX)
CREATE TABLE #AgesServedGroup(
       provider_id INT, Infant VarChar(4), Preschool VarChar(4),
       SchoolAge VarChar(4), Adolescent VarChar(4), YoungAdult VarChar(4),
       Transition VarChar(4), Adult VarChar(4), Senior VarChar(4))

BEGIN
   SET @SQL = 'INSERT INTO #AgesServedGroup (provider_id, Infant, Preschool,
               SchoolAge,Adolescent,YoungAdult,Transition,Adult,Senior)
           SELECT provider_id,
             CASE WHEN provider_age_served_infant = 1 THEN 8 END AS Infant,
             CASE WHEN provider_age_served_pre_school = 1 THEN 13 END AS Preschool,
             CASE WHEN provider_age_served_school_age = 1 THEN 12 END AS SchoolAge,
             CASE WHEN provider_age_served_adolescent = 1 THEN 76 END AS Adolescent,
             CASE WHEN provider_age_served_young_adult = 1 THEN 77  END AS YoungAdult,
             CASE WHEN provider_age_served_transition = 1 THEN 78 END AS Transition,
             CASE WHEN provider_age_served_adult = 1 THEN 79  END AS Adult,
             CASE WHEN provider_age_served_senior = 1 THEN 80  END AS Senior
          FROM  dbo.provider_age_served'
     EXEC (@SQL);
END

 --Combine the ages served code
 CREATE TABLE #AgesServed(
         provider_id int,
         AgesServed VarChar(100))

BEGIN
    SET @SQL = 'INSERT INTO #AgesServed (provider_id, AgesServed)
                SELECT provider_id, Infant + Preschool + SchoolAge +
                      Adolescent + YoungAdult + Transition + Adult + Senior
               FROM #AgesServedGroup'
    EXEC (@SQL);
END

INSERT INTO ProviderExport (link_name, cat_id, link_featured)
SELECT provider_id,CONVERT(varchar(100),AgesServed), 0
FROM #AgesServed 

DROP TABLE #AgesServedGroup
DROP TABLE #AgesServed

EDIT AGAIN

--Create the Ages Served Codes
  CREATE TABLE #AgesServedGroup(
  provider_id INT, Infant VarChar(4), Preschool VarChar(4), SchoolAge VarChar(4), Adolescent   VarChar(4), 
  YoungAdult VarChar(4), Transition VarChar(4), Adult VarChar(4), Senior VarChar(4))

  BEGIN
  INSERT INTO #AgesServedGroup (provider_id, Infant, Preschool, SchoolAge,Adolescent,YoungAdult, Transition,Adult,Senior) 
  SELECT provider_id, CASE WHEN provider_age_served_infant = 1 THEN 8 END AS Infant, 
          CASE WHEN provider_age_served_pre_school = 1 THEN 13 END AS Preschool, 
          CASE WHEN provider_age_served_school_age = 1 THEN 12 END AS SchoolAge, 
          CASE WHEN provider_age_served_adolescent = 1 THEN 76 END AS Adolescent, 
          CASE WHEN provider_age_served_young_adult = 1 THEN 77 END AS YoungAdult, 
          CASE WHEN provider_age_served_transition = 1 THEN 78 END AS Transition, 
          CASE WHEN provider_age_served_adult = 1 THEN 79 END AS Adult, 
          CASE WHEN provider_age_served_senior = 1 THEN 80 END AS Senior
   FROM  dbo.provider_age_served        
  END

  --Combine the ages served code
  CREATE TABLE #AgesServed(
  provider_id int,  AgesServed VarChar(100))

  BEGIN
    INSERT INTO #AgesServed (provider_id, AgesServed) SELECT provider_id,
    CASE WHEN Infant Is Not Null THEN Infant + ',' ELSE '' END +
    CASE WHEN Preschool Is Not Null THEN Preschool + ',' ELSE '' END +  
    CASE WHEN SchoolAge Is Not Null THEN SchoolAge + ',' ELSE '' END +  
    CASE WHEN Adolescent Is Not Null THEN Adolescent + ',' ELSE '' END +  
    CASE WHEN YoungAdult Is Not Null THEN YoungAdult + ',' ELSE '' END +  
    CASE WHEN Transition Is Not Null THEN Transition + ',' ELSE '' END +  
    CASE WHEN Adult Is Not Null THEN Adult + ',' ELSE '' END + 
    CASE WHEN Senior Is Not Null THEN Senior ELSE '' END FROM #AgesServedGroup      
  END

  --Add the ages served master code
  BEGIN
    UPDATE #AgesServed SET AgesServed = '1,' + AgesServed WHERE AgesServed <> ''
  END

  --Trim the trailing comma
  BEGIN
    UPDATE #AgesServed SET AgesServed = LEFT(AgesServed, len(AgesServed) -1) WHERE RIGHT(AgesServed,1) = ','
  END

Upvotes: 1

Views: 3847

Answers (2)

rjdevereux
rjdevereux

Reputation: 1892

For MS-SQL it's going to depend on the data types for the columns and if they are nullable, but something like this should work if everything is a *char type and not null.

select  1 as id
        ,'Hello' as col1
        ,' ' as col2
        ,'world' as col3
        ,'!' as col4
into newtable_tbl

select id
      , col1 + ',' + col2 + ',' + col3 + ',' + col4
from  newtable_tbl

Upvotes: 1

jmoreno
jmoreno

Reputation: 13561

First off, this sounds like a horrible idea.

But here's what it would look like.

create table #ccat (id int identity (1,1) primary key, MasterCommaDelimColumn varchar(max));

Insert into #ccat (MasterCommaDelimColumn)
Select cast(c1 as varchar)
        + ','+ cast(c2 as varchar)
        + ','+ cast(c3 as varchar)
from (values(1,2,3),(3,4,5),(6,7,8))g(c1,c2,c3);

select * from #ccat;

drop table #ccat;

Upvotes: 1

Related Questions