Row mapping and generating equivalent records for given condition in sql server

In this article, i will demonstrate a solution for dynamic row mapping problem in sql server. We will see “row mapping for given condition” with examples and in various way. One of my colleague asked me a question as below;

“I have a table with Male and Female genders and want an output by mapping each Male with a Female and vice versa and in case any Gender has less number of records than another, “NA” should be displayed with opposite Gender name.”

i.e. If i have a table named #Players with columns Name and Gender. Now if table has 3 Males and 2 Females named Ram, Shyam, Amit as males and Sita, Radha as Females, output should be as below;

Output:

Solution :- 1

In this problem we are not going to use any dynamic sql and we will use SET based approach to achieve the output.

Create table statement;

CREATE TABLE #Players
(
NAME VARCHAR(100),
GENDER VARCHAR(100)
)

Inserting demo records;

INSERT INTO #Players VALUES('Ram', 'Male')
INSERT INTO #Players VALUES('Shyam','Male')
INSERT INTO #Players VALUES('Amit','Male')
INSERT INTO #Players VALUES('Sita','Female')
INSERT INTO #Players VALUES('Radha','Female')
INSERT INTO #Players VALUES('Puja','Female')
INSERT INTO #Players VALUES('Archana','Female')

Now have a look on the below query;

SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS SeqMain, CASE WHEN Name = '' THEN 'NA' ELSE Name END AS Name, Gender FROM
(
SELECT * FROM #Players WHERE GENDER = 'MALE'
UNION ALL
SELECT TOP
(CASE WHEN ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE')) > 0 THEN
((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE'))
ELSE 0 END)
'', 'Male    ' FROM #Players
WHERE ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE')) > 0
)DTMALE

UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY NAME), * FROM
(
SELECT * FROM #Players WHERE GENDER = 'FEMALE'
UNION ALL
SELECT TOP
(CASE WHEN ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE')) > 0 THEN
((SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE'))
ELSE 0 END)
'', 'Female' FROM #Players
WHERE ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE')) > 0

) DTFEMALE
ORDER BY SeqMain DESC, GENDER DESC

In the above query i have extracted all the males with an union of as much blank rows as they are less from another Gender and the same process is used for Female with an Union of as much rows as they are less from Males.

SELECT TOP
(CASE WHEN ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE')) > 0 THEN
((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE'))
ELSE 0 END)
'', 'Male    ' FROM #Players
WHERE ((SELECT COUNT(1) FROM #Players WHERE GENDER = 'FEMALE') - (SELECT COUNT(1) FROM #Players WHERE GENDER = 'MALE')) > 0

Now have a look on the above part of the query. Here we have put the TOP condition dynamically with a to get only as much needed records to make it equivalent with another gender. In case it’s a negative number, i have replaced it with 0 to avoid error.

Now i have created two derived tables DTMALE and DTFEMALE, in which i just added a serial number for Males and Females respectively. And then applied an UNION operator by ordering ot with Seq number to make it sort and to display the final output.

Another approach to solve the same problem can be as below;

Solution :- 2

Have a look on the below query;

SELECT * FROM

(

SELECT *, RANK() OVER (PARTITION BY T.GENDER ORDER BY T.NAME) 'ID' FROM #Players T

UNION ALL

SELECT 'NA', CASE WHEN COALESCE(XX.GENDER,zz.GENDER) = 'MALE' THEN 'FEMALE' ELSE 'MALE' END, COALESCE(XX.ID, ZZ.ID)

FROM

(

SELECT *, RANK() OVER (PARTITION BY T.GENDER ORDER BY T.NAME) 'ID' FROM #Players T WHERE T.GENDER = 'MALE') XX

FULL OUTER JOIN

(SELECT *,RANK() OVER (PARTITION BY T.GENDER ORDER BY T.NAME) 'ID' FROM #Players T WHERE T.GENDER = 'FEMALE') ZZ

ON XX.ID = ZZ.ID where (xx.NAME IS NULL OR zz.NAME IS NULL)

) DT

ORDER BY ID DESC, GENDER DESC

In this query first i have partitioned the table on Gender and the applied an UNION by dynamically generating only as much records as needed to make the records equal with opposite gender with opposite gender and NA.

Conclusion

Now it’s on you , which method you like. You can also try some other methods. I have shared what i have found interested and optimal.

Share your feedback and comments on this article.