Splitting dynamic values in two columns into multiple columns - Duplicate Removal

Question

Splitting dynamic values in two columns into multiple columns - Duplicate Removal

I am trying to create a query that can split multiple values in a column into multiple columns to help “remove duplicates” of the dataset.

It is best explained in the data below, but basically you will notice the interval field, which is DENSE RANK by the columns ID, START, FINISH, DURATION, COD. Due to multiple overlapping PSSID and CSSID values, these intervals are duplicated. I would like to know if there is a good way to dynamically split overlapping PSSID and CSSID fields into multiple columns ...! Okay, so what I really mean ...

SAMPLES DATA:

ID  START   FINISH  DURA    COD INT PSSID   CSSID
A1  33.18   33.27   0.09    ST  15  N13045  NULL
A1  33.18   33.27   0.09    ST  15  N13046  NULL
A1  33.27   33.285  0.015   DU  16  N13046  NULL
A1  33.27   33.285  0.015   DU  16  NULL    N20015
A1  33.27   33.285  0.015   DU  16  NULL    N2001516
A1  33.27   33.285  0.015   DU  16  NULL    N20033
A1  33.285  33.35   0.065   BM  17  N13046  NULL
A1  33.285  33.35   0.065   BM  17  NULL    N20015
A1  33.285  33.35   0.065   BM  17  NULL    N2001516
A1  33.285  33.35   0.065   BM  17  NULL    N20033
A1  33.35   33.395  0.045   DM  18  N13046  NULL
A1  33.35   33.395  0.045   DM  18  NULL    N20015
A1  33.35   33.395  0.045   DM  18  NULL    N2001516
A1  33.35   33.395  0.045   DM  18  NULL    N20033
A1  33.395  33.44   0.045   DN  19  N13046  NULL
A1  33.395  33.44   0.045   DN  19  NULL    N20015
A1  33.395  33.44   0.045   DN  19  NULL    N2001516
A1  33.395  33.44   0.045   DN  19  NULL    N20033
A1  33.44   33.485  0.045   BM  20  N13046  NULL
A1  33.44   33.485  0.045   BM  20  NULL    N2001516
A1  33.44   33.485  0.045   BM  20  NULL    N20033
A1  33.44   33.485  0.045   BM  20  NULL    N20034
A1  33.485  33.51   0.025   DN  21  N13046  NULL
A1  33.485  33.51   0.025   DN  21  NULL    N2001516
A1  33.485  33.51   0.025   DN  21  NULL    N20033
A1  33.485  33.51   0.025   DN  21  NULL    N20034
A1  33.51   33.595  0.085   DB  22  N13046  NULL
A1  33.51   33.595  0.085   DB  22  NULL    N2001516
A1  33.51   33.595  0.085   DB  22  NULL    N20034
A1  33.595  33.665  0.07    DN  23  N13046  NULL
A1  33.595  33.665  0.07    DN  23  NULL    N2001516
A1  33.595  33.665  0.07    DN  23  NULL    N20034
A1  33.665  33.785  0.12    DB  24  NULL    N2001516
A1  33.785  33.79   0.005   YS  25  NULL    NULL
A1  33.79   33.83   0.04    BM  26  NULL    NULL

DESIRED OUTPUT:

ID  START   FINISH  DURA    COD INT PSSID1  PSSID2  CSSID1  CSSID2      CSSID3
A1  33.18   33.27   0.09    ST  15  N13046  N13045  NULL    NULL        NULL
A1  33.27   33.285  0.015   DU  16  N13046  NULL    N20015  N2001516    N20033
A1  33.285  33.35   0.065   BM  17  N13046  NULL    N20015  N2001516    N20033
A1  33.35   33.395  0.045   DM  18  N13046  NULL    N20015  N2001516    N20033
A1  33.395  33.44   0.045   DN  19  N13046  NULL    N20015  N2001516    N20033
A1  33.44   33.485  0.045   BM  20  N13046  NULL    N20034  N2001516    N20033
A1  33.485  33.51   0.025   DN  21  N13046  NULL    N20034  N2001516    N20033
A1  33.51   33.595  0.085   DB  22  N13046  NULL    N20034  N2001516    NULL
A1  33.595  33.665  0.07    DN  23  N13046  NULL    N20034  N2001516    NULL
A1  33.665  33.785  0.12    DB  24  NULL    NULL    NULL    N2001516    NULL
A1  33.785  33.79   0.005   YS  25  NULL    NULL    NULL    NULL        NULL
A1  33.79   33.83   0.04    BM  26  NULL    NULL    NULL    NULL        NULL

, , PSSID, CSSID ( 5). , .

SQL Server 2012. :

CREATE TABLE #SampleData
    ([ID] varchar(2), [START] decimal(9,2), [FINISH] decimal(9,2), [DURA] decimal(9,2), [COD] varchar(2), [INT] int, [PSSID] varchar(6), [CSSID] varchar(8))
;

INSERT INTO #SampleData
    ([ID], [START], [FINISH], [DURA], [COD], [INT], [PSSID], [CSSID])
VALUES
    ('A1', 33.18, 33.27, 0.09, 'ST', 15, 'N13045', NULL),
    ('A1', 33.18, 33.27, 0.09, 'ST', 15, 'N13046', NULL),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, 'N13046', NULL),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N20015'),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N2001516'),
    ('A1', 33.27, 33.285, 0.015, 'DU', 16, NULL, 'N20033'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, 'N13046', NULL),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N20015'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N2001516'),
    ('A1', 33.285, 33.35, 0.065, 'BM', 17, NULL, 'N20033'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, 'N13046', NULL),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N20015'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N2001516'),
    ('A1', 33.35, 33.395, 0.045, 'DM', 18, NULL, 'N20033'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, 'N13046', NULL),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N20015'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N2001516'),
    ('A1', 33.395, 33.44, 0.045, 'DN', 19, NULL, 'N20033'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, 'N13046', NULL),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N2001516'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N20033'),
    ('A1', 33.44, 33.485, 0.045, 'BM', 20, NULL, 'N20034'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, 'N13046', NULL),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N2001516'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N20033'),
    ('A1', 33.485, 33.51, 0.025, 'DN', 21, NULL, 'N20034'),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, 'N13046', NULL),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, NULL, 'N2001516'),
    ('A1', 33.51, 33.595, 0.085, 'DB', 22, NULL, 'N20034'),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, 'N13046', NULL),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, NULL, 'N2001516'),
    ('A1', 33.595, 33.665, 0.07, 'DN', 23, NULL, 'N20034'),
    ('A1', 33.665, 33.785, 0.12, 'DB', 24, NULL, 'N2001516'),
    ('A1', 33.785, 33.79, 0.005, 'YS', 25, NULL, NULL),
    ('A1', 33.79, 33.83, 0.04, 'BM', 26, NULL, NULL)
;

!

+4

sql database sql-server sql-server-2012

Harry 13 . '16 6:33

2

gotqn · Answer 1 · 2016-07-13T06:55:40+0000

, INT. , pivot PSS CSS , .

SELECT *
INTO #DataSourcePSS
FROM
(
    SELECT [INT]
          ,[PSSID]
          ,CONCAT('PSSID',ROW_NUMBER() OVER (PARTITION BY [INT] ORDER BY [PSSID] DESC)) AS [RowID]
    FROM #SampleData
) DS
PIVOT
(
    MAX([PSSID]) FOR RowID IN ([PSSID1], [PSSID2], [PSSID3], [PSSID4], [PSSID5])
) PVT

SELECT *
INTO #DataSourceCSS
FROM
(
    SELECT [INT]
          ,[CSSID]
          ,CONCAT('CSSID', ROW_NUMBER() OVER (PARTITION BY [INT] ORDER BY [CSSID] DESC)) AS [RowID] 
    FROM #SampleData
) DS
PIVOT
(
    MAX([CSSID]) FOR RowID IN ([CSSID1], [CSSID2], [CSSID3], [CSSID4], [CSSID5])
) PVT;

WITH DataSourceSD AS 
(
    SELECT DISTINCT [ID], [START], [FINISH], [DURA], [COD], [INT]
    FROM #SampleData
)
SELECT SD.*
      ,PSS.[PSSID1],PSS.[PSSID2],PSS.[PSSID3],PSS.[PSSID4],PSS.[PSSID5]
      ,CSS.[CSSID1],CSS.[CSSID2],CSS.[CSSID3],CSS.[CSSID4],CSS.[CSSID5]
FROM DataSourceSD SD
INNER JOIN #DataSourcePSS PSS
    ON SD.[INT] = PSS.[INT]
INNER JOIN #DataSourceCSS CSS
    ON SD.[INT] = CSS.[INT]
ORDER BY SD.[INT];

DROP TABLE #DataSourceCSS;
DROP TABLE #DataSourcePSS;
DROP TABLE #SampleData;

, . , . OK, PIVOT.

Shnugo · Answer 2 · 2016-07-13T08:20:25+0000

:

PSSID CSSID. , 4 , N20034 . , , ... . relativ "parent", , ...

WITH Numbered AS
(
    SELECT *
          ,CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END AS ColumnName  
          ,ROW_NUMBER() OVER(PARTITION BY ID,Start,Finish,Dura,COD,[INT],CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END ORDER BY (SELECT NULL)) AS SortNr  
    FROM #SampleData
)
,DistinctPSSIDS AS
(
    SELECT DISTINCT
           DENSE_RANK() OVER(ORDER BY PSSID) AS SortNr
          ,PSSID
    FROM #SampleData
    WHERE PSSID IS NOT NULL
)
,DistinctCSSIDS AS
(
    SELECT DISTINCT
           DENSE_RANK() OVER(ORDER BY CSSID) AS SortNr
          ,CSSID
    FROM #SampleData
    WHERE CSSID IS NOT NULL
)
SELECT ID,Start,Finish,Dura,COD,[INT]
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=1 THEN n.PSSID END) AS PSSID1
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=2 THEN n.PSSID END) AS PSSID2
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=3 THEN n.PSSID END) AS PSSID3
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=4 THEN n.PSSID END) AS PSSID4
      ,MAX(CASE WHEN n.ColumnName='PSSID' AND dp.SortNr=5 THEN n.PSSID END) AS PSSID5
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=1 THEN n.CSSID END) AS CSSID1
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=2 THEN n.CSSID END) AS CSSID2
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=3 THEN n.CSSID END) AS CSSID3
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=4 THEN n.CSSID END) AS CSSID4
      ,MAX(CASE WHEN n.ColumnName='CSSID' AND dc.SortNr=5 THEN n.CSSID END) AS CSSID5
FROM Numbered AS n
LEFT JOIN DistinctPSSIDS AS dp ON dp.PSSID=n.PSSID
LEFT JOIN DistinctCSSIDS AS dc ON dc.CSSID=n.CSSID
GROUP BY ID,Start,Finish,Dura,COD,[INT]

+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| ID | Start | Finish | Dura | COD | INT | PSSID1 | PSSID2 | PSSID3 | PSSID4 | PSSID5 | CSSID1 | CSSID2   | CSSID3 | CSSID4 | CSSID5 |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.18 | 33.27  | 0.09 | ST  | 15  | N13045 | N13046 | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.27 | 33.29  | 0.02 | DU  | 16  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.29 | 33.35  | 0.07 | BM  | 17  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.35 | 33.40  | 0.05 | DM  | 18  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.40 | 33.44  | 0.05 | DN  | 19  | NULL   | N13046 | NULL   | NULL   | NULL   | N20015 | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.44 | 33.49  | 0.05 | BM  | 20  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033 | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.49 | 33.51  | 0.03 | DN  | 21  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033 | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.51 | 33.60  | 0.09 | DB  | 22  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.60 | 33.67  | 0.07 | DN  | 23  | NULL   | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | N20034 | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.67 | 33.79  | 0.12 | DB  | 24  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.79 | 33.79  | 0.01 | YS  | 25  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+
| A1 | 33.79 | 33.83  | 0.04 | BM  | 26  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+--------+----------+--------+--------+--------+

previous: old-fashion-pivot GROUP BY

WITH Numbered AS
(
    SELECT *
          ,CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END AS ColumnName  
          ,ROW_NUMBER() OVER(PARTITION BY ID,Start,Finish,Dura,COD,[INT],CASE WHEN PSSID IS NOT NULL THEN 'PSSID' ELSE 'CSSID' END ORDER BY (SELECT NULL)) AS SortNr  
    FROM #SampleData
)
SELECT ID,Start,Finish,Dura,COD,[INT]
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=1 THEN PSSID END) AS PSSID1
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=2 THEN PSSID END) AS PSSID2
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=3 THEN PSSID END) AS PSSID3
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=4 THEN PSSID END) AS PSSID4
      ,MAX(CASE WHEN ColumnName='PSSID' AND SortNr=5 THEN PSSID END) AS PSSID5
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=1 THEN CSSID END) AS CSSID1
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=2 THEN CSSID END) AS CSSID2
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=3 THEN CSSID END) AS CSSID3
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=4 THEN CSSID END) AS CSSID4
      ,MAX(CASE WHEN ColumnName='CSSID' AND SortNr=5 THEN CSSID END) AS CSSID5
FROM Numbered
GROUP BY ID,Start,Finish,Dura,COD,[INT]

+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| ID | Start | Finish | Dura | COD | INT | PSSID1 | PSSID2 | PSSID3 | PSSID4 | PSSID5 | CSSID1   | CSSID2   | CSSID3 | CSSID4 | CSSID5 |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.18 | 33.27  | 0.09 | ST  | 15  | N13045 | N13046 | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.27 | 33.29  | 0.02 | DU  | 16  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.29 | 33.35  | 0.07 | BM  | 17  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.35 | 33.40  | 0.05 | DM  | 18  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.40 | 33.44  | 0.05 | DN  | 19  | N13046 | NULL   | NULL   | NULL   | NULL   | N20015   | N2001516 | N20033 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.44 | 33.49  | 0.05 | BM  | 20  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033   | N20034 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.49 | 33.51  | 0.03 | DN  | 21  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20033   | N20034 | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.51 | 33.60  | 0.09 | DB  | 22  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20034   | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.60 | 33.67  | 0.07 | DN  | 23  | N13046 | NULL   | NULL   | NULL   | NULL   | N2001516 | N20034   | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.67 | 33.79  | 0.12 | DB  | 24  | NULL   | NULL   | NULL   | NULL   | NULL   | N2001516 | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.79 | 33.79  | 0.01 | YS  | 25  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+
| A1 | 33.79 | 33.83  | 0.04 | BM  | 26  | NULL   | NULL   | NULL   | NULL   | NULL   | NULL     | NULL     | NULL   | NULL   | NULL   |
+----+-------+--------+------+-----+-----+--------+--------+--------+--------+--------+----------+----------+--------+--------+--------+

Splitting dynamic values ​​in two columns into multiple columns - Duplicate Removal

previous: old-fashion-pivot GROUP BY

More articles:

Splitting dynamic values in two columns into multiple columns - Duplicate Removal