How to create a custom table? 1

[ronaldmacdonald]

I have a table with the contents below:

Field1 Field2 Field3
1 JOHN DOE
2 JOHNNY SMITH
3 MR JONES
3 MRS JONES
4 MR PATTER
4 MRS PATTER

I want to create a table with the contents below from the above:

Field1 Field2 Field3 Field4 Field5
1 JOHN DOE
2 JOHNNY SMITH
3 MR JONES MRS JONES
4 MR PATTER MRS PATTER

Is anything like this possible in sql?

RonaldMacdonald

 

[TamarGranor]

The key here is to use the original table twice in the query, but you also need a way to distinguish the first instance of Field1 from the second.

SELECT T1.Field1, T1.Field2, T1.Field3, 
       ISNULL(T2.Field2, '') AS Field4, ISNULL(T2.Field3, '') AS Field5
  FROM YourTable T1 
    LEFT JOIN YourTable T2 
      ON T1.Field1 = T2.Field1 
      AND T1.Field2 < T2.Field2

Tamar

 

[ronaldmacdonald]

Wow tamar - genius.
It gives the correct effect but leaves me with duplicate lines (as I have other fields)

Field1 Field2 Field3 Field4 Field5
1 JOHN DOE
2 JOHNNY SMITH
3 MR JONES
3 MR JONES MRS JONES
5 MR PATTER
4 MR PATTER MRS PATTER

 

[ronaldmacdonald]

Sorted it now by using the query below - which removed the duplicate rows.

USE XX
GO
WITH duplicates AS( SELECT ROW_NUMBER() OVER(PARTITION BY Field1 ORDER BY CLAIMID) AS dups,
Field1,
Field2,
Field3,
Field4
FROM Yourtable
DELETE duplicates WHERE dups > 1;

GO