unpivot column

PeterP1550 80 Reputation points
2023-07-25T21:07:13.7833333+00:00
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.

User's image

SQL Server
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
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,376 Reputation points
    2023-07-25T21:30:23.9333333+00:00

    @PeterP1550,

    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, ',');
    
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.