how can I join Column value with fix limit of 2 and group the column in SQL?-CodePudding

I have one table as Images.

TableName    ColumnName     RecordId    Caption          ImageType      ROTId     DetailRecordId
Table2      PAUTDPhoto_bin  1462        test            PAUTPhotos      1041383   11480170
Table2      PAUTDPhoto_bin  1463        test1           PAUTPhotos      1041383   11480170
Table2      PAUTDPhoto_bin  1464        testing photo   PAUTPhotos      1041383   11480170
Table1      ItemPhoto_bin   11480170    caption          ItemPhoto      1041383   11480170
Table1      ItemPhoto_bin   11480171    test photo       ItemPhoto      1041383   11480171
Table1      ItemPhoto_bin   11480172    description      ItemPhoto      1041383   11480172
Table2      PAUTDPhoto_bin  1465        test             PAUTPhotos     1041383   11480172
Table2      PAUTDPhoto_bin  1466        55               PAUTPhotos     1041383   11480172

My output select query will do the following actions,

1.) Need to do GroupBy by DetailRecordId and ColumnName.

2.) After GroupBy I want to combine RecordId and Caption columns. Need to combine only 2 values into one.

My output should be,

TableName    ColumnName     RecordId    Caption          ImageType      ROTId     DetailRecordId
Table2      PAUTDPhoto_bin  1462$1463   test$test1       PAUTPhotos     1041383   11480170
Table2      PAUTDPhoto_bin  1464        testing photo    PAUTPhotos     1041383   11480170
Table1      ItemPhoto_bin   11480170    caption          ItemPhoto      1041383   11480170
Table1      ItemPhoto_bin   11480171    test photo       ItemPhoto      1041383   11480171
Table1      ItemPhoto_bin   11480172    description      ItemPhoto      1041383   11480172
Table2      PAUTDPhoto_bin  1465$1466   test$55          PAUTPhotos     1041383   11480172

Note: I want to join RecordId and Caption columns with only 2 values into that.

CodePudding user response：

We can use LEAD to pull the next row for any given DetailRecordId ColumnName pair, and use modulo (%) to only grab the 1st, 3rd, 5th row, and so on.

WITH cte AS
(
  SELECT 
    TableName,
    ColumnName,
    RecordId,
    ImageType,
    ROTId,
    DetailRecordId, 
    NewRecordId = CONCAT(RTRIM(RecordId), '$' 
         LEAD(RecordId,1) OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)),
    NewCaption = CONCAT(RTRIM(Caption), '$' 
         LEAD(Caption,1) OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)),
    rn = ROW_NUMBER() OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)
  FROM dbo.YourTableName
)
SELECT 
  TableName,
  ColumnName,
  RecordId = NewRecordId,
  Caption = NewCaption,
  ImageType,
  ROTId,
  DetailRecordId
FROM cte 
WHERE rn % 2 = 1;

Working example in this fiddle.

The repeated OVER() clauses kind of bother me; in SQL Server 2022, we'll be able to simplify this greatly with the new "sharable" WINDOW clause:

WITH cte AS
(
  SELECT 
    TableName,
    ColumnName,
    RecordId,
    ImageType,
    ROTId,
    DetailRecordId, 
    NewRecordId = CONCAT(RTRIM(RecordId), '$'   LEAD(RecordId,1) OVER W),
    NewCaption  = CONCAT(RTRIM(Caption),  '$'   LEAD(Caption,1) OVER W),
    rn = ROW_NUMBER() OVER W
  FROM dbo.YourTableName
  -- magic vvv
  WINDOW W AS ( PARTITION BY DetailRecordId, ColumnName ORDER BY ColumnName) 
  -- magic ^^^
)
SELECT 
  TableName,
  ColumnName,
  RecordId = NewRecordId,
  Caption = NewCaption,
  ImageType,
  ROTId,
  DetailRecordId
FROM cte 
WHERE rn % 2 = 1;

CodePudding user response：

If your SQL version supports the STRING_AGG function you may try the following:

SELECT MAX(TableName) TableName, ColumnName,STRING_AGG(D.RecordId, '$') RecordId,
       STRING_AGG(D.Caption, '$') Caption, MAX(ImageType) ImageType, 
       MAX(ROTId) ROTId, MAX(DetailRecordId) DetailRecordId
FROM
(
  SELECT *,
         (COUNT(*) OVER (PARTITION BY ColumnName, DetailRecordId ORDER BY RecordId)   1) /2  grp
  FROM table_name
) D
GROUP BY ColumnName, DetailRecordId, D.grp
ORDER BY  DetailRecordId, ColumnName

For other versions:

WITH CTE AS
  (
   SELECT *,
         (COUNT(*) OVER (PARTITION BY ColumnName, DetailRecordId ORDER BY RecordId)   1) /2  grp
   FROM table_name
  )

SELECT MAX(TableName) TableName, ColumnName,
       STUFF((SELECT '$'   CAST(T.RecordId as VARCHAR(MAX))
              FROM CTE T WHERE T.ColumnName = D.ColumnName AND
                               T.DetailRecordId = D.DetailRecordId AND
                               T.grp = D.grp 
              FOR xml PATH ('')), 1, 1, '') RecordId,
       STUFF((SELECT '$'   CAST(T.Caption as VARCHAR(MAX))
              FROM CTE T WHERE T.ColumnName = D.ColumnName AND
                               T.DetailRecordId = D.DetailRecordId AND
                               T.grp = D.grp 
              FOR xml PATH ('')), 1, 1, '') Caption,
       MAX(ImageType) ImageType, MAX(ROTId) ROTId, MAX(DetailRecordId) DetailRecordId
FROM CTE D
GROUP BY ColumnName, DetailRecordId, D.grp
ORDER BY  DetailRecordId, ColumnName

See a demo.