Insert/merge multiple records into a single one using a variable table to print on postcard both sides

Question

I have a task of printing a set of addresses on a postcard on both sides. On the front the owner address will print centered, and on the back the parcel number, address, along with other data.

Four postcards are per sheet of paper. I know how to do one sided printing for postcards, my challenge is now that I have to do both sides. So I think that first, I need to align the data so what prints on the back, corresponds to what prints on the front.

I think that in order to be able to print both sides using SSRS I need to change how the records are retrieved.

Each record has 6 columns. I need to print 4 addresses per page (both sides when printing)

My data as it looks currently:

ParcelNumber    OwnerName       OwnerAddress                Owner_City_State_Zip        ParcelAddress           parcel_city_state_zip
70918302278     Smith, John     15 Dimondi Ct               Dover, DE, 19901            15 White Oak St         Laurel, DE, 19956
70918403133     Perez, Victor   8619 S Wolcott Ave          Chicago, IL, 60620          6634 W 64th Pl          Chicago, IL, 60638
70918404058     Flying, Lotus   5527 E Hazel Mountain Rd    Cleveland, VA, 24225        1925 Freedom Ln         Falls Church, VA, 22043
70919107370     Pastorius, Jaco 1549 Highland Dr            Lake Geneva, WI, 53147      415 S Wells St          Lake Geneva, WI, 53147
70823107036     Bocelli, Andrea 1310 Fillmore St            San Francisco, CA, 94115    1099 Fillmore St #4R    San Francisco, CA, 94115
70918221072     Ttracks, Shaun  516 Manford Rd SW           Atlanta, GA, 30310          480 Riverside Dr NW     Atlanta, GA, 30328
70919110092     Little, John    242 W Hudson St             Long Beach, NY, 11561       38 Vincent St           Rockville Centre, NY, 11570

I was thinking that one way to make this work would be to insert 4 records into a single row, so that the output looks like this (sorry for the length):

ID  Parcel1_1   Owner_1         OwnerAddress_1      OwnerCityStateZip_1         ParcelAddress_1         ParcelCityStateZip_1        Parcel1_2   Owner_2         OwnerAddress_2      OwnerCityStateZip_2 ParcelAddress_2     ParcelCityStateZip_2    Parcel1_3   Owner_3         OwnerAddress_3              OwnerCityStateZip_3     ParcelAddress_3 ParcelCityStateZip_3        Parcel1_4   Owner_4             OwnerAddress_4      OwnerCityStateZip_4     ParcelAddress_4 ParcelCityStateZip_4
1   70918302278 Smith, John     15 Dimondi Ct       Dover, DE, 19901            15 White Oak St         Laurel, DE, 19956           70918403133 Perez, Victor   8619 S Wolcott Ave  Chicago, IL, 60620  6634 W 64th Pl      Chicago, IL, 60638      70918404058 Flying, Lotus   5527 E Hazel Mountain Rd    Cleveland, VA, 24225    1925 Freedom Ln Falls Church, VA, 22043     70919107370 Pastorius, Jaco     1549 Highland Dr    Lake Geneva, WI, 53147  415 S Wells St  Lake Geneva, WI, 53147
2   70823107036 Bocelli, Andrea 1310 Fillmore St    San Francisco, CA, 94115    1099 Fillmore St #4R    San Francisco, CA, 94115    70918221072 Tracks, Shaun   516 Manford Rd SW   Atlanta, GA, 30310  480 Riverside Dr NW Atlanta, GA, 30328      70919110092 Little, John    242 W Hudson St             Long Beach, NY, 11561   38 Vincent St   Rockville Centre, NY, 11570 70918214126 Zidane, Zinedine    724 Hill Ave        Pittsburgh, PA, 15221   711 Copeland St Pittsburgh, PA, 15232
and so on...

The table would look something like this:

DECLARE @FOUR_INTO_ONE AS TABLE
(   ID_NUM INT IDENTITY(1,1)
,   PARCEL_1 VARCHAR(20)    
,   OWNERNAME_1 VARCHAR(200)
,   OWNERADDRESS_1 VARCHAR(300)
,   OWNERCITYSTATEZIP_1 VARCHAR(300)
,   PARCELADDRESS_1 VARCHAR(300)
,   PARCELCITYSTATEZIP_1 VARCHAR(300)
,   PARCEL_2 VARCHAR(20)    
,   OWNERNAME_2 VARCHAR(200)
,   OWNERADDRESS_2 VARCHAR(300)
,   OWNERCITYSTATEZIP_2 VARCHAR(300)
,   PARCELADDRESS_2 VARCHAR(300)
,   PARCELCITYSTATEZIP_2 VARCHAR(300)
,   PARCEL_3 VARCHAR(20)    
,   OWNERNAME_3 VARCHAR(200)
,   OWNERADDRESS_3 VARCHAR(300)
,   OWNERCITYSTATEZIP_3 VARCHAR(300)
,   PARCELADDRESS_3 VARCHAR(300)
,   PARCELCITYSTATEZIP_3 VARCHAR(300)
,   PARCEL_4 VARCHAR(20)    
,   OWNERNAME_4 VARCHAR(200)
,   OWNERADDRESS_4 VARCHAR(300)
,   OWNERCITYSTATEZIP_4 VARCHAR(300)
,   PARCELADDRESS_4 VARCHAR(300)
,   PARCELCITYSTATEZIP_4 VARCHAR(300))

I think this could potentially work since I would have more control as to how and where to place the values of each record page 1 and page 2.

I am not sure how to complete this using SQL, is this where a CURSOR is used? how many loops are needed, is there a simple way to manipulate the data so the out put looks like the one I need?

EDIT: I tried to follow an example that uses STUFF in this question

SELECT
    Parcel = 
        STUFF((
            SELECT [ParcelNumber]
            FROM [dbo].[BI_RPEC] 
            WHERE [ParcelNumber] = t.[ParcelNumber]            
            FOR XML PATH(''), TYPE
        ).value('text()[1]','NVARCHAR(MAX)'), 1, 1, N'')
 FROM [dbo].[BI_RPEC] t
WHERE 
    t.ParcelNumber IN ('070823107036','070919107370','070919110092','070918404058','070918403133','070918302278','070918221072', '070918214126')

But is retrieving only NULLS.

am I missing the identifier qualifier? I replaced it with WHERE s.ParcelNumber IN ('070823107036','070919107370','070919110092','070918404058','070918403133','070918302278','070918221072', '070918214126') but it made even bigger mess. — erasmo carlos, Dec 09 '19 at 22:54
The point is that the FOR XML in that sql groups those with the same ParcelNumber. But in your data all the ParcelNumber are unique. So you need something else to get them in groups of 4. Anyway, what version of ms sql server? Also that Sql isn't for pivoting anyway. — LukStorms, Dec 09 '19 at 22:58
I apologize I really don't know how to do this, but I would like to learn. If you would be willing to offer a hand, that would be awesome. Thank you. — erasmo carlos, Dec 09 '19 at 23:13

LukStorms · Accepted Answer · 2019-12-10T00:30:42.943

You can pivot them over a calculated rownumber.

Ranked by division by 4 & assigned by modulus 4.

;WITH CTE_DATA AS
(
    SELECT *
  , ROW_NUMBER() OVER (ORDER BY OwnerAddress, ParcelNumber) AS RowNum
    FROM YourTable
)
SELECT 
  MAX(CASE WHEN RowNum%4 = 1 THEN ParcelNumber END) AS ParcelNumber_1
, MAX(CASE WHEN RowNum%4 = 1 THEN OwnerName END) AS OwnerName_1
, MAX(CASE WHEN RowNum%4 = 1 THEN OwnerAddress END) AS OwnerAddress_1
, MAX(CASE WHEN RowNum%4 = 1 THEN Owner_City_State_Zip END) AS Ownercitystatezip_1
, MAX(CASE WHEN RowNum%4 = 1 THEN ParcelAddress END) AS Parceladdress_1
, MAX(CASE WHEN RowNum%4 = 1 THEN parcel_city_state_zip END) AS Parcelcitystatezip_1

, MAX(CASE WHEN RowNum%4 = 2 THEN ParcelNumber END) AS ParcelNumber_2
, MAX(CASE WHEN RowNum%4 = 2 THEN OwnerName END) AS OwnerName_2
, MAX(CASE WHEN RowNum%4 = 2 THEN OwnerAddress END) AS OwnerAddress_2
, MAX(CASE WHEN RowNum%4 = 2 THEN Owner_City_State_Zip END) AS Ownercitystatezip_2
, MAX(CASE WHEN RowNum%4 = 2 THEN ParcelAddress END) AS Parceladdress_2
, MAX(CASE WHEN RowNum%4 = 2 THEN parcel_city_state_zip END) AS Parcelcitystatezip_2

, MAX(CASE WHEN RowNum%4 = 3 THEN ParcelNumber END) AS ParcelNumber_3
, MAX(CASE WHEN RowNum%4 = 3 THEN OwnerName END) AS OwnerName_3
, MAX(CASE WHEN RowNum%4 = 3 THEN OwnerAddress END) AS OwnerAddress_3
, MAX(CASE WHEN RowNum%4 = 3 THEN Owner_City_State_Zip END) AS Ownercitystatezip_3
, MAX(CASE WHEN RowNum%4 = 3 THEN ParcelAddress END) AS Parceladdress_3
, MAX(CASE WHEN RowNum%4 = 3 THEN parcel_city_state_zip END) AS Parcelcitystatezip_3

, MAX(CASE WHEN RowNum%4 = 0 THEN ParcelNumber END) AS ParcelNumber_4
, MAX(CASE WHEN RowNum%4 = 0 THEN OwnerName END) AS OwnerName_4
, MAX(CASE WHEN RowNum%4 = 0 THEN OwnerAddress END) AS OwnerAddress_4
, MAX(CASE WHEN RowNum%4 = 0 THEN Owner_City_State_Zip END) AS Ownercitystatezip_4
, MAX(CASE WHEN RowNum%4 = 0 THEN Parceladdress END) AS Parceladdress_4
, MAX(CASE WHEN RowNum%4 = 0 THEN parcel_city_state_zip END) AS Parcelcitystatezip_4

FROM CTE_DATA
GROUP BY FLOOR((1.0*RowNum-1)/4)

This solution was perfect. I truly thank you for your help. – erasmo carlos Dec 10 '19 at 23:01 — erasmo carlos, Dec 10 '19 at 23:01

BJones · Answer 2 · 2019-12-10T16:16:01.720

Perhaps this will help (I added a few more rows for testing).

CREATE TABLE FOUR_INTO_ONE
(   ID_NUM INT IDENTITY(1,1)
,   ParcelNumber VARCHAR(20)    
,   OwnerName VARCHAR(200)
,   OwnerAddress VARCHAR(300)
,   Owner_City_State_Zip VARCHAR(300)
,   ParcelAddress VARCHAR(300)
,   parcel_city_state_zip VARCHAR(300)
)
GO
INSERT INTO FOUR_INTO_ONE
VALUES
('70918302278','Smith, John','15 Dimondi Ct','Dover, DE, 19901','15 White Oak St','Laurel, DE, 19956'),
('70918403133','Perez, Victor','8619 S Wolcott Ave','Chicago, IL, 60620','6634 W 64th Pl','Chicago, IL, 60638'),
('70918404058','Flying, Lotus','5527 E Hazel Mountain Rd','Cleveland, VA, 24225','1925 Freedom Ln','Falls Church, VA, 22043'),
('70919107370','Pastorius, Jaco','1549 Highland Dr','Lake Geneva, WI, 53147','415 S Wells St','Lake Geneva, WI, 53147'),
('70823107036','Bocelli, Andrea','1310 Fillmore St','San Francisco, CA, 94115','1099 Fillmore St #4R','San Francisco, CA, 94115'),
('70918221072','Ttracks, Shaun','516 Manford Rd SW','Atlanta, GA, 30310','480 Riverside Dr NW','Atlanta, GA, 30328'),
('70919110092','Little, John','242 W Hudson St','Long Beach, NY, 11561','38 Vincent St','Rockville Centre, NY, 11570'),

('70919104616','Pastorius, J','1549 Highland Dr','Lake Geneva, WI, 53147','415 S Wells St','Lake Geneva, WI, 53147'),
('70823108161','Bocelli, And','1310 Fillmore St','San Francisco, CA, 94115','1099 Fillmore St #4R','San Francisco, CA, 94115'),
('70918226456','Ttracks, Sha','516 Manford Rd SW','Atlanta, GA, 30310','480 Riverside Dr NW','Atlanta, GA, 30328'),
('70919111061','Little, Jo','242 W Hudson St','Long Beach, NY, 11561','38 Vincent St','Rockville Centre, NY, 11570')
GO




SELECT STUFF((SELECT '; ' + ParcelNumber + ' ' + OwnerName + ' ' + OwnerAddress + ' ' + Owner_City_State_Zip + ' ' + ParcelAddress + ' ' + parcel_city_state_zip
               FROM (SELECT DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR,
                        f.id_num,f.ParcelNumber,f.OwnerName,f.OwnerAddress,f.Owner_City_State_Zip,f.ParcelAddress,f.parcel_city_state_zip
                        FROM FOUR_INTO_ONE F) fio
               WHERE fio.dr = t.DR
               ORDER BY id_num
               FOR XML PATH('')), 1, 1, '') FIO
FROM(
    SELECT
    DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR,
    f.id_num,f.ParcelNumber,f.OwnerName,f.OwnerAddress,f.Owner_City_State_Zip,f.ParcelAddress,f.parcel_city_state_zip
    FROM FOUR_INTO_ONE F
    )t
GROUP BY t.DR
--ORDER BY ID_NUM

Results are concatenated strings

70918302278 Smith, John 15 Dimondi Ct Dover, DE, 19901 15 White Oak St Laurel, DE, 19956; 70918403133 Perez, Victor 8619 S Wolcott Ave Chicago, IL, 60620 6634 W 64th Pl Chicago, IL, 60638; 70918404058 Flying, Lotus 5527 E Hazel Mountain Rd Cleveland, VA, 24225 1925 Freedom Ln Falls Church, VA, 22043; 70919107370 Pastorius, Jaco 1549 Highland Dr Lake Geneva, WI, 53147 415 S Wells St Lake Geneva, WI, 53147
70823107036 Bocelli, Andrea 1310 Fillmore St San Francisco, CA, 94115 1099 Fillmore St #4R San Francisco, CA, 94115; 70918221072 Ttracks, Shaun 516 Manford Rd SW Atlanta, GA, 30310 480 Riverside Dr NW Atlanta, GA, 30328; 70919110092 Little, John 242 W Hudson St Long Beach, NY, 11561 38 Vincent St Rockville Centre, NY, 11570; 70919104616 Pastorius, J 1549 Highland Dr Lake Geneva, WI, 53147 415 S Wells St Lake Geneva, WI, 53147
70823108161 Bocelli, And 1310 Fillmore St San Francisco, CA, 94115 1099 Fillmore St #4R San Francisco, CA, 94115; 70918226456 Ttracks, Sha 516 Manford Rd SW Atlanta, GA, 30310 480 Riverside Dr NW Atlanta, GA, 30328; 70919111061 Little, Jo 242 W Hudson St Long Beach, NY, 11561 38 Vincent St Rockville Centre, NY, 11570

Edit To get everything in a separate column you can try:

SELECT *
FROM(SELECT *
     FROM(
        SELECT ROW_NUMBER() OVER(PARTITION BY t.DR ORDER BY t.ID_NUM) AS RN, t.*
        FROM(SELECT DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR, f.* FROM FOUR_INTO_ONE f)t
         )tt WHERE tt.RN = 1) rn1
LEFT JOIN
(SELECT * 
     FROM(
         SELECT ROW_NUMBER() OVER(PARTITION BY t.DR ORDER BY t.ID_NUM) AS RN, t.*
         FROM(SELECT DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR, f.* FROM FOUR_INTO_ONE f)t
         )t1 WHERE t1.RN = 2)rn2 ON rn2.DR = rn1.DR
LEFT JOIN
(SELECT * 
     FROM(
         SELECT ROW_NUMBER() OVER(PARTITION BY t.DR ORDER BY t.ID_NUM) AS RN, t.*
         FROM(SELECT DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR, f.* FROM FOUR_INTO_ONE f)t
         )t1 WHERE t1.RN = 3)rn3 ON rn3.DR = rn1.DR
LEFT JOIN
(SELECT * 
     FROM(
         SELECT ROW_NUMBER() OVER(PARTITION BY t.DR ORDER BY t.ID_NUM) AS RN, t.*
         FROM(SELECT DENSE_RANK() OVER(PARTITION BY f.ID_NUM % 4 ORDER BY f.ID_NUM) DR, f.* FROM FOUR_INTO_ONE f)t
         )t1 WHERE t1.RN = 4)rn4 ON rn4.DR = rn1.DR

I am just getting back on this task. thank you so much for your help. I am going to give a shot to both ideas and report back. Once again, thank you! — erasmo carlos, Dec 10 '19 at 15:26
@erasmocarlos, sure, see the edit. It's a bit convoluted, but it works. — BJones, Dec 10 '19 at 16:16
I agree, the query has been running for 7 minutes and continues to run. Thank you fo r making the change though. Much appreciated. — erasmo carlos, Dec 10 '19 at 17:34

Insert/merge multiple records into a single one using a variable table to print on postcard both sides

2 Answers2