Raj De Inno Raj De Inno - 1 year ago 65
SQL Question

Split records into 2 columns based on record count in SQL Server

I am trying to select records from a single table. I have two columns to show the records.

I have to split the records based on the count. That is, I need to part the records into 20 rows for each section.

Additionally, in the event that I have more than 40 records, it needs to show the records in column 1.

Please refer the attached screenshot:

enter image description here

Records should be like this:

enter image description here

I have tried the below query in a stored procedure, but looks like I'm missing something. If the record count greater than 40, it's not retrieving as expected.

Any help would be really appreciated.

CREATE proc mytest2
AS
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1

IF OBJECT_ID('tempdb..#test2') IS NOT NULL
DROP TABLE #test2

SELECT
ROW_NUMBER() OVER (ORDER BY rno) 'rno',
Reg, SerialNo
INTO
#test1
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ID) 'rno',
Reg, SerialNo
FROM
tRe) AS c
WHERE
rno % 20 = 0

SELECT
ROW_NUMBER() OVER (ORDER BY rno) 'rno1',
Reg AS Reg1, SerialNo AS SerialNo1
INTO
#test2
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ID) 'rno',
Reg, SerialNo
FROM
tRe) AS c
WHERE
rno % 20 <> 1

SELECT *
FROM #test1 a
FULL OUTER JOIN #test2 b ON a.rno = b.rno1

------


EXEC mytest2

Answer Source

Change your where condition to : For First Query :

 WHERE (rno % 20 > 0 AND (rno / 20) % 2 = 0)  OR     (rno % 20 = 0 AND (rno / 20) % 2 = 1)

For Second Query :

 WHERE (rno % 20 > 0 AND (rno / 20) % 2 = 1)   OR    (rno % 20 = 0 AND (rno / 20) % 2 = 0)

That is to say Your Query Will be:

IF OBJECT_ID('tempdb..#test1') IS NOT NULL
    DROP TABLE #test1

IF OBJECT_ID('tempdb..#test2') IS NOT NULL
    DROP TABLE #test2 

SELECT  ROW_NUMBER() OVER ( ORDER BY rno ) 'rno' ,
        Reg ,
        SerialNo
INTO    #test1
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY EmployeeId ) 'rno' ,
                    Reg ,
                    SerialNo
          FROM      tRe
        ) AS c
WHERE   ( rno % 20 > 0
          AND ( rno / 20 ) % 2 = 0
        )
        OR ( rno % 20 = 0
             AND ( rno / 20 ) % 2 = 1
           )

SELECT  ROW_NUMBER() OVER ( ORDER BY rno ) 'rno1' ,
        Reg AS Reg1 ,
        SerialNo AS SerialNo1
INTO    #test2
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY EmployeeId ) 'rno' ,
                    Reg ,
                    SerialNo
          FROM      tRe
        ) AS c
WHERE   ( rno % 20 > 0
          AND ( rno / 20 ) % 2 = 1
        )
        OR ( rno % 20 = 0
             AND ( rno / 20 ) % 2 = 0
           )

SELECT  *
FROM    #test1 a
        FULL OUTER JOIN #test2 b ON a.rno = b.rno1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download