Generate 1 and 2 numbers alternatively using t-sql

Hi,

I am not sure, maybe this is what you are looking for, please try below code:

CREATE TABLE #TMP
(
ID SMALLINT,
NAME VARCHAR(20)
);

INSERT INTO #TMP(ID,NAME)
VALUES 
(1,'Sam'),
(1,'John'),
(1,'Greg'),
(1,'Smith'),
(1,'Aron'),
(2,'Brad'),
(2,'Kevin'),
(2,'Alex'),
(2,'Jeff');

WITH CTE
AS
(
        SELECT ID
                ,NAME
                ,NTILE(2) OVER(PARTITION BY ID ORDER BY ID) AS RN
        FROM #TMP
        --ORDER BY ID
)
SELECT ID
        ,CASE WHEN RN=1 THEN NAME ELSE '' END AS NAME1
        ,CASE WHEN RN=1 THEN RN ELSE '' END AS RN1
        ,CASE WHEN RN=2 THEN NAME ELSE '' END AS NAME2
        ,CASE WHEN RN=2 THEN RN ELSE '' END AS RN2
FROM CTE

Regards,

Reshma

---

Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered