Created a table called SegmentDetails. It has 2 columns Segment and Details. Segment contains the category name while Details contains the member names. Each segment has multiple members and are part of a single record separated by Comma. Each member should have an individual record and should be split accordingly displaying the segment and individual member name.
Example: If a segment has 10 member names in a single record separated by comma, there should be 10 records for that segment showing segment name and the individual member name
1) Table SegmentDetails and its records
2) Table SegmentDetails and is records after splitting them into multiple records
This Script can be used and modified as per needs. For example, if the values are separated by semi-colon, the semi-colon can be subsituted in place of Comma in the script and this script is given below.
SELECT Segment,
SUBSTRING(Details, n, CHARINDEX(',', Details + ',', n) - n) AS Details
FROM SegmentDetails
CROSS JOIN (SELECT number FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 1000) AS Numbers(n)
WHERE SUBSTRING(',' + Details, n, 1) = ','
AND n < LEN(Details) + 1
Example: If a segment has 10 member names in a single record separated by comma, there should be 10 records for that segment showing segment name and the individual member name
1) Table SegmentDetails and its records
This Script can be used and modified as per needs. For example, if the values are separated by semi-colon, the semi-colon can be subsituted in place of Comma in the script and this script is given below.
SELECT Segment,
SUBSTRING(Details, n, CHARINDEX(',', Details + ',', n) - n) AS Details
FROM SegmentDetails
CROSS JOIN (SELECT number FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 1000) AS Numbers(n)
WHERE SUBSTRING(',' + Details, n, 1) = ','
AND n < LEN(Details) + 1
No comments:
Post a Comment