|
/* 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.