Click here to Skip to main content
12,241,564 members (46,234 online)
Click here to Skip to main content
Articles » Database » Database » SQL » Downloads

Stats

23.1K views
84 downloads
6 bookmarked
Posted

Generating and Splitting a Delimited String Column

, 17 Jan 2013 CPOL
This article describes the easiest way to create and process delimited row using SQL XQuery.
/* create table @DeveloperProject */
DECLARE @DeveloperProject Table(    
    DeveoperID INT,    
    ProjectCode  VARCHAR(20)
) 

/* insert dummy data for the table @DeveloperProject */
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'REPT_MOD')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(3,'REPT_MOD')

/* creating delimited project codes */
SELECT DeveoperID,
    REPLACE((SELECT 
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

SELECT DeveoperID,
    SUBSTRING((SELECT 
        ',' + ProjectCode
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), 2, 8000) AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

/* explanation data() function; project code with code attribute */
SELECT ProjectCode 
FROM @DeveloperProject c2      
FOR XML PATH('')

/* project code without attribute */
SELECT ProjectCode AS 'data()'
FROM @DeveloperProject c2      
FOR XML PATH('')


/* create table to store comma seperated project codes */
DECLARE @DeveloperProjectCSV Table(    
    DeveoperID INT,    
    ProjectCodes  VARCHAR(MAX)
)

/* insert data to the table @DeveloperProjectCSV */
INSERT INTO @DeveloperProjectCSV(DeveoperID,ProjectCodes)
SELECT DeveoperID,
    REPLACE((SELECT 
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

SELECT * FROM @DeveloperProjectCSV

/* splitting the comma seperated project list */
;WITH cte AS (
    SELECT 
        DeveoperID,
        CAST('<r>' + REPLACE(ProjectCodes, ',', '</r><r>') + '</r>' AS XML) AS ProjectCodes
    FROM @DeveloperProjectCSV
)
SELECT 
    DeveoperID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS ProjectCode
FROM cte
CROSS APPLY ProjectCodes.nodes('//r') AS xTable(xColumn)

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tharaka MTR
Technical Lead Eyepax IT Consulting (Pvt) Ltd.
Sri Lanka Sri Lanka
Having more than 9 year hands-on industry experience in software development
Responsible for designing, implementing and managing complex software systems with stringent up-time requirement.

Visit my blog

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 18 Jan 2013
Article Copyright 2013 by Tharaka MTR
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid