1

At the moment, my data looks like this with all 3 preferences separated by comma... 

Subscriber ID                      Preference
34732632746                      Abc123, abc1234, bcd232

I want to duplicate the subscriber ID and separate the preference onto different lines... like this:

Subscriber ID                      Preference
34732632746                      Abc123
34732632746                      abc1234 
34732632746                      bcd232
Reuben McCabe
  • 11
  • 1
  • 1
    What's your dbms? – D-Shih Jul 09 '19 at 01:42
  • When you added the SQL tag, you were shown a suggestion that you also add a tag for the specific DBMS you're using, because syntax and functionality between them varies. Please [edit] your post now to add that tag, as any attempt to answer without it wastes both your time and ours. When you make that [edit], it would also be helpful if you added your own effort to solve the problem as well. – Ken White Jul 09 '19 at 01:45
  • You should also search this site for `[sql] comma separated values into rows` (including the square brackets) for other similar questions. You can refine the results of that search by also adding the tag (in square brackets) for the specific DBMS you're using. Chances are quite good that it's been asked and answered here before. – Ken White Jul 09 '19 at 01:50
  • You shouldn't be storing comma separated values in a single column to begin with –  Jul 09 '19 at 05:45

2 Answers2

0

If you used SQL Server 2017 then please check below query.

SELECT 
      SubscriberID,Value AS Preference 
FROM @tblName 
    CROSS APPLY STRING_SPLIT(Preference, ',');

If you used a lower version of SQL Server 2017 then please add split string function and used below query.

-----Query------------

SELECT SubscriberID,Item AS Preference from @tbl CROSS APPLY dbo.SplitString(Preference, ',');

-----Query------------

-----Function---------

CREATE FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

-----Function---------

Hemang A
  • 1,012
  • 1
  • 5
  • 16
0

You didn't specify your DBMS product, but if you use Postgres, you can use unnest() and string_to_array()

select t.subscriber_id
       p.preference
from the_table t
  cross join lateral unnest(string_to_array(p.preference, ',')) as p(preference);