SQlServer Concatenating row values from a column in a table using ‘For XML’

When writing ETL jobs in SQL, transforming data between values in a column in table and a string representation (like all values concatenated in a comma separated string) can be a required step.

In my recent ETL project, while trying to de-normalize an Entity-attribute-value data model to a data warehouse friendly schema, I encountered a similar problem.

I had to concatenate the attribute names into a comma separated list, so I could generate the desired schema and issue queries using the ‘IN’ statement. I think I’d leave the details of the job and discussions or reasoning regarding my approach.

I was able to accomplish this using the‘FOR XML’ and a correlated subquery in sql server as shown below.

CREATE TABLE #EAV_Test (

 ID int not null,

  code VARCHAR(30) NOT NULL,

  value VARCHAR(200),

  CONSTRAINT pk_EAV_Test

  PRIMARY KEY (ID, code));

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Risk', 'High');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'Priority', 'Med');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'type', 'personal');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'date', '20080215');

INSERT INTO #EAV_Test (ID,code, value) VALUES(1,'amount', '3500.00');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Risk', 'Low');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'Priority', 'High');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'type', 'open');

INSERT INTO #EAV_Test (ID,code, value) VALUES(2,'amount', '250.00');

SELECT ID, code, value FROM #EAV_Test;

/* Get all distinct codes for an ID */

Select distinct ID,

stuff((select ','+cast(code as varchar(1000))

        from #EAV_Test as T2

        where T1.ID = T2.ID 

        for xml path(''), type).value('.', 'varchar(max)'),1,1,'') AllCodes

from #EAV_Test T1

/* Results for previous query

ID     AllCodes

1      amount, date, Priority, Risk, type

2      amount, Priority, Risk, type

*/

/* Get all values for each code */

Select distinct code,

stuff((select ','+cast(value as varchar(1000))

        from #EAV_Test as T2

        where T1.code = T2.code

        for xml path(''), type).value('.', 'varchar(max)'),1,1,'') AllCodes

from #EAV_Test T1

/* Results for previous query

code   AllCodes

amount 3500.00, 250.00

date   20080215

Priority      Med, High

Risk   High, Low

type   personal, open

*/

drop table #EAV_Test