Search code examples
sqlsql-servert-sqljoinpivot

Pivot rows into columns with duplicate ID

I have a table with below o/p enter image description here I want the o/p to be presented as I have tried using Pivot function & case function of sql server but this is removing the duplicate row.

Here is the sample table with insert statement

Rowid  Title  FirstName  MiddleName  Surname     Pun    DateOfBirth    Gender
230417  Null   Fewa        Null      Deductible  e0-xx   Null          Null
230417  Null   lotsa       Null      Deductible  b5-xx   Null          Null
230418  Null   Mary        Null      Albrook     66-xx   1987-05-25    Null
230418  Null   Nick        Null      Albrook     a8-xx   1988-06-12    Null
230419  Mr     Yfgzaitbzg  Twopass   Tpajyqhong  84-xx   1957-12-31    M

CREATE TABLE 
[dbo].[temp]
(
   [rowid] [int] NULL,
   [key] [nvarchar](4000) NULL,
   [value] [nvarchar](max) NULL
   ) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'FirstName', N'Fewa');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'Surname', N'Deductibles');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'Pun', N'e0f64678-b116-4354-9c6b-70a31d966bf3');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'Pun', N'0be046ce-1060-45a2-a148-9f73b429fcdb');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'FirstName', N'Ncdzvcddoh');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'Surname', N'Dpugcyrnpy');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230421, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'FirstName', N'Lotsa');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'Surname', N'Deductibles');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230417, N'Pun', N'b5c648c5-0573-46b0-bc9d-4c4e24cbb1f1');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Pun', N'e7e44938-2e84-4ed7-8dd4-d544e0d6e949');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Title', N'MS');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'FirstName', N'Cvcwzkuaom');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Surname', N'Rnkbzmbbkd');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'DateOfBirth', N'2003-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Gender', N'F');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Pun', N'5fa38b17-7c3a-4ef5-af9f-49a725eacee7');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'FirstName', N'Mhmbyykrqm');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Surname', N'Fgiqtzbhid');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Pun', N'5248c731-f0a2-4257-9b49-1822985c1437');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Title', N'MR');`
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'FirstName', N'Jalwlkgiuv');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'MiddleName', N'Pass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Surname', N'Bseefgbfon');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'DateOfBirth', N'1971-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230424, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Pun', N'5d3d9305-5d25-45f6-b910-8dcfede04ab7');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Title', N'MS');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'FirstName', N'Qanmrsthree');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Surname', N'Regression');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'DateOfBirth', N'2003-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Gender', N'F');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Pun', N'675813d0-28fb-4420-ab30-8e56b9c70661');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'FirstName', N'Zixdatkbru');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'MiddleName', N'Pass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Surname', N'Uzcpzvyfxf');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'DateOfBirth', N'2003-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230425, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Pun', N'c0d1b53b-fbc8-40b7-af66-110ca36337c8');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Title', N'MRS');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'FirstName', N'Muufesuovs');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Surname', N'Bsztkklgio');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'DateOfBirth', N'1956-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230426, N'Gender', N'F');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Pun', N'af775b03-30e2-4075-b46b-7646ed4451cc');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'FirstName', N'Tvcssggxse');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'MiddleName', N'Referme');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Surname', N'Jgacfmsusk');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'DateOfBirth', N'2003-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Pun', N'fa7a1fbb-c3f7-4173-9b9c-0ce9ed111320');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Title', N'MRS');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'FirstName', N'Upzuyqynge');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'MiddleName', N'Referme');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Surname', N'Klpaivegks');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'DateOfBirth', N'2003-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230422, N'Gender', N'F');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Pun', N'623df730-6918-495a-8397-e52524b95b2e');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'FirstName', N'Jake');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Surname', N'Reese');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Pun', N'e54ab6bb-3e44-45c4-97d4-3edec001cc2a');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Title', N'MS');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'FirstName', N'Holly');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Surname', N'Ganger');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230423, N'Gender', N'F');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'Pun', N'722f3856-9ff5-432d-a8e2-9341194a8666');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'FirstName', N'Jake');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'Surname', N'Reese');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230420, N'Gender', N'M');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'FirstName', N'Mary');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'Surname', N'Allbrook');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'Pun', N'665aa63f-6fb4-4fd2-8f44-274b0915314c');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'DateOfBirth', N'1987-05-25T00:00:00+00:00');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'FirstName', N'Nick');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'Surname', N'Allbrook');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'Pun', N'a8ac6be9-e43e-4263-b2e4-4bc4f9d0b5d3');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230418, N'DateOfBirth', N'1988-06-12T00:00:00+00:00');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'Pun', N'8400db27-1674-4da0-8b82-c341a9973dab');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'Title', N'MR');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'FirstName', N'Yfgzaitbzg');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'MiddleName', N'Twopass');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'HasMiddleName', N'true');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'Surname', N'Tpajyqhong');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'DateOfBirth', N'1957-12-31T00:00:00Z');
   GO

   INSERT [dbo].[temp] ([rowid], [key], [value]) VALUES (230419, N'Gender', N'M');
   GO`
Solution

  • If I am not wrong then, you want to transform the data in your table into a format where each unique rowid becomes a single row with columns for Title, FirstName, MiddleName, Surname, Pun, DateOfBirth, and Gender.

    DECLARE @Columns NVARCHAR(MAX) = N'';
SELECT @Columns = STRING_AGG(QUOTENAME([key]), ',') WITHIN GROUP (ORDER BY [key])
FROM (SELECT DISTINCT [key] FROM [temp]) AS KeyValues;

DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'
WITH NumberedRows AS (
  SELECT
    [rowid],
    [key],
    [value],
    ROW_NUMBER() OVER (PARTITION BY [rowid], [key] ORDER BY (SELECT NULL)) AS rn
  FROM [temp]
)
SELECT
  [rowid], ' + @Columns + '
FROM NumberedRows
PIVOT (
  MAX([value]) FOR [key] IN (' + @Columns + ')
) AS PivotTable
ORDER BY [rowid];';

EXEC sp_executesql @DynamicSQL;

    This query will pivot the data in your table dynamically, creating columns for each distinct "key" value. Check the example here.