Apologies in advance if this can't be done, I'm very inexperienced in SQL server. I have a table like this:

And I would like to pivot the last two columns, but keep the core columns, 'id' and 'name' the same so we end up with

I was considering creating a temp tables with Pet and Pet_Name and joining them and creating a new column 'name' in this temp table with the owner name, then joining this to the original table on 'name' - but this sounds very convoluted. Is there a cleaner way to do this perhaps?

CodePudding user response：

Please try the following solution.

It is using JSON and will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, _Name VARCHAR(20), Pet VARCHAR(MAX), Pet_Name VARCHAR(MAX));
INSERT INTO @tbl (_Name, Pet, Pet_Name) VALUES
('Adam', 'Dog, Dog, Cat', 'Cooper, Boot, Snowflake'),
('Ben', 'Dog, Hamster, Cat', 'Cliff, Hammy, Tiger');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS 
(
     SELECT ID, _Name
         , s = '["'   REPLACE(REPLACE(pet,SPACE(1),''), @separator, '","')   '"]'
         , s1 = '["'   REPLACE(REPLACE(Pet_Name,SPACE(1),''), @separator, '","')   '"]'
     FROM @tbl
 )
 SELECT ID, _Name, pet.[value] AS pet_name, pet_name.[value] AS pet_name
 FROM rs
    CROSS APPLY OPENJSON (s, N'$') AS pet
    CROSS APPLY OPENJSON (s1, N'$') AS pet_name
 WHERE pet.[key] = pet_name.[key];

Output

 ---- ------- ---------- ----------- 
| ID | _Name | pet_name | pet_name  |
 ---- ------- ---------- ----------- 
|  1 | Adam  | Dog      | Cooper    |
|  1 | Adam  | Dog      | Boot      |
|  1 | Adam  | Cat      | Snowflake |
|  2 | Ben   | Dog      | Cliff     |
|  2 | Ben   | Hamster  | Hammy     |
|  2 | Ben   | Cat      | Tiger     |
 ---- ------- ---------- ----------- 

CodePudding user response：

My friend, I hope this piece of code helps you to achieve what you want.

First of all, I prefer to have a Table-valued function to use in general not just in a query. This function returns a table with two columns ItemIndex and ItemCharacter:

CREATE FUNCTION [dbo].[StrigSplitFunction]( 
  @String varchar(max)
 ,@Separator varchar(10))
RETURNS TABLE 
AS
RETURN (
   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS ItemIndex, value AS 
   ItemCharacter
   FROM STRING_SPLIT(@String, @Separator)
)
GO

Then I use this function in select query to generate what you want from your table:

SELECT T.Id
    , T.[Name]
    , A.ItemCharacter AS Pet
    , (SELECT B.ItemCharacter FROM [StrigSplitFunction] (T.Pet_Name,',') AS 
B WHERE B.ItemIndex = A.ItemIndex)  AS Pet_Name
FROM [TableName] AS T
CROSS APPLY (SELECT * FROM [StrigSplitFunction] (T.Pet,',')) as A

If you need any more description write for me in comment.

CodePudding user response：

Table Data Input:

IF OBJECT_ID('tempdb.dbo.#TempTbl') IS NOT NULL
    DROP TABLE #TempTbl;
GO

CREATE TABLE #TempTbl
(
    ID INT NULL,
    Name VARCHAR(4000) NULL,
    Pet VARCHAR(4000) NULL,
    Pet_Name VARCHAR(4000) NULL
);

INSERT INTO #TempTbl
    (ID, Name, Pet, Pet_Name)
VALUES
    (1, 'Adam', 'Dog, Dog, Cat', 'Cooper, Boot, Snowflake'),
    (2, 'Ben', 'Dog, Hamster, Cat', 'Cliff, Hammy, Tiger');

--
SELECT * FROM #TempTbl;

Code to Split the String Respectively: Works with any version of SQL2008 and above

;WITH _CSV_Pet
AS (
       SELECT ID,
              Name,
              LTRIM(RTRIM(SUBSTRING((Pet   ','), 1, CHARINDEX(',', (Pet   ','), 1) - 1))) AS "val",
              LTRIM(RTRIM(SUBSTRING((Pet   ','), CHARINDEX(',', (Pet   ','), 1)   1, LEN((Pet   ','))))) AS "rem"
       FROM #TempTbl
       UNION ALL
       SELECT a.ID,
              a.Name,
              LTRIM(RTRIM(SUBSTRING(a.rem, 1, CHARINDEX(',', a.rem, 1) - 1))) AS "val",
              LTRIM(RTRIM(SUBSTRING(a.rem, CHARINDEX(',', a.rem, 1)   1, LEN(a.rem))))
       FROM _CSV_Pet AS a
       WHERE LEN(a.rem) >= 1
   ),
     _CSV_Pet_Name
AS (
       SELECT ID,
              Name,
              LTRIM(RTRIM(SUBSTRING((Pet_Name   ','), 1, CHARINDEX(',', (Pet_Name   ','), 1) - 1))) AS "val",
              LTRIM(RTRIM(SUBSTRING((Pet_Name   ','), CHARINDEX(',', (Pet_Name   ','), 1)   1, LEN((Pet_Name   ','))))) AS "rem"
       FROM #TempTbl
       UNION ALL
       SELECT a.ID,
              a.Name,
              LTRIM(RTRIM(SUBSTRING(a.rem, 1, CHARINDEX(',', a.rem, 1) - 1))) AS "val",
              LTRIM(RTRIM(SUBSTRING(a.rem, CHARINDEX(',', a.rem, 1)   1, LEN(a.rem))))
       FROM _CSV_Pet_Name AS a
       WHERE LEN(a.rem) >= 1
   )
SELECT CSV1.ID,
       CSV1.Name,
       CSV1.Value AS "Pet",
       CSV2.Value AS "Pet_Name"
FROM (
         SELECT CSV.ID,
                CSV.Name,
                ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS "Idx",
                CSV.val AS "Value"
         FROM _CSV_Pet AS CSV
     ) AS CSV1
JOIN (
         SELECT CSV.ID,
                CSV.Name,
                ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS "Idx",
                CSV.val AS "Value"
         FROM _CSV_Pet_Name AS CSV
     ) AS CSV2
    ON CSV2.ID = CSV1.ID AND CSV2.Idx = CSV1.Idx
ORDER BY CSV1.ID ASC
OPTION (MAXRECURSION 0);