How do I generate a query that gives me results like this:
Nombre nombre_sigue
David Lopez danilo cruz
Carlos perez sebastian corea
Juana maria maria dinora
Patricia aguilar kurt heiz
in this example there are 10 names in an employee table with a name field. I do not want to show the result in only one field, but in two from 1 to 5 in one column and from 6 to 10 in another. any ideas? subqueries?
If your table has a structure like the following:
Table1
What I can think of is the following with a subquery.
SELECT Top(10) A.nombres AS 'Nombre', (SELECT B.nombres
FROM Tabla1 AS B
WHERE B.id = (A.id + 10)) AS 'nombre_sigue'
FROM Tabla1 AS A
ORDER BY A.id
With this you achieve the following:
Greetings.
This is an alternative that basically divides a set of rows into two columns, it also allows the amount to be odd.
DECLARE @Personas TABLE (
Nombre VARCHAR(255)
)
INSERT INTO @Personas (Nombre)
SELECT 'David Lopez' UNION
SELECT 'danilo cruz' UNION
SELECT 'Carlos perez' UNION
SELECT 'sebastian corea' UNION
SELECT 'Juana maria' UNION
SELECT 'maria dinora' UNION
SELECT 'Patricia aguilar' UNION
SELECT 'kurt heiz' UNION
SELECT 'Luis Perez'
;WITH CTE AS (
SELECT Nombre,
ROW_NUMBER() OVER(ORDER BY Nombre) AS RN,
(SELECT COUNT(1) FROM @Personas) AS CT
FROM @Personas
)
SELECT T1.Nombre,
T2.Nombre
FROM CTE T1
LEFT JOIN CTE T2
ON T2.rn = T1.RN + CEILING(T1.CT/2.0)
WHERE T1.RN <= CEILING(T1.CT/2.0)
Exit:
+--------------+------------------+
| Carlos perez | Luis Perez |
+--------------+------------------+
| danilo cruz | maria dinora |
+--------------+------------------+
| David Lopez | Patricia aguilar |
+--------------+------------------+
| Juana maria | sebastian corea |
+--------------+------------------+
| kurt heiz | NULL |
+--------------+------------------+
Explanation:
@Personas with the data you gave, plus an additional case to make the odd list and test the operation in these cases JOIN between the registers on the left and the right side