SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,479 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
drop TABLE Table1
CREATE TABLE Table1
(
fname VARCHAR(25) NOT NULL,
mname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
val VARCHAR(500) NOT NULL
)
INSERT INTO Table1 VALUES
('Bob', 'John', 'Smith', 'apple, orange, grape')
INSERT INTO Table1 VALUES
('Amy', 'Jen', 'Jones', 'pear, orange, apricot, lemon, lime')
How do you return the results like this.
Please try the following solution. It will work starting from SQL Server 2016 onwards.
DECLARE @tbl TABLE (
fname VARCHAR(25) NOT NULL,
mname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
val VARCHAR(500) NOT NULL
);
INSERT @tbl VALUES
('Bob', 'John', 'Smith', 'apple, orange, grape'),
('Amy', 'Jen', 'Jones', 'pear, orange, apricot, lemon, lime');
SELECT fname, mname, lname
, value
FROM @tbl
CROSS APPLY STRING_SPLIT(val, ',');