CODEWITHSUNDEEP
sql-server
user9933478
user9933478

Reputation:

SQL Server: how count from value from dynamic columns?

SQL Server: how count from value from dynamic columns?
I have data:

+ Subject
___________________
| SubID | SubName |
|-------|---------|
| 1     | English |
|-------|---------|
| 2     | Spanish |
|-------|---------|
| 3     | Korean  |
|_______|_________|

+ Student
______________________________________
| StuID   | StuName | Gender | SubID  |
|---------|---------|--------|--------|
| 1       | David   | M      | 1,2    |
|---------|---------|--------|--------|
| 2       | Lucy    | F      | 2,3    |
|_________|_________|________|________|

I want to query result as:

____________________________________
| SubID  | SubName | Female | Male |
|--------|---------|--------|------|
| 1      | English | 0      | 1    |
|--------|---------|--------|------|
| 2      | Spanish | 1      | 1    |
|--------|---------|--------|------|
| 3      | Koean   | 1      | 0    |
|________|_________|________|______|


This is my query:

SELECT
     SubID, SubName, 0 AS Female, 0 AS Male
FROM Subject

I don't know to replace 0 with real count.

Upvotes: 1

Views: 361

Answers (2)

Will Kong
Will Kong

Reputation: 71

If the version of your SQL Server is SQL Server or above, you could use STRING_split function to get expected results.

create table Subjects
(
 SubID int,
 SubName varchar(30)
 )

 insert into Subjects values
 (1,'English'),
 (2,'Spanish'),
 (3,'Korean')

create table student
(
StuID int,
StuName varchar(30),
Gender varchar(10),
SubID  varchar(10)
)

insert into student values
(1,'David','M','1,2'),
(2,'Lucy','F','2,3')

--Query
;WITH CTE AS
(
SELECT 
S.Gender,
S1.value AS SubID
FROM student S
CROSS APPLY  STRING_SPLIT(S.SubID,',') S1
)
select 
T.SubID,
T.SubName,
COUNT(CASE T1.Gender WHEN 'F' THEN 1 END) AS Female,
COUNT(CASE T1.Gender WHEN 'M' THEN 1 END) AS Male
from Subjects T
LEFT JOIN CTE T1 ON T.SubID=T1.SubID
GROUP BY T.SubID,T.SubName
ORDER BY T.SubID

--Output
/*
SubID       SubName                        Female      Male
----------- ------------------------------ ----------- -----------
1           English                        0           1
2           Spanish                        1           1
3           Korean                         1           0
*/

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

Because you made the mistake of storing CSV data in your tables, we will have to do some SQL Olympics to get your result set. We can try joining the two tables on the condition that the SubID from the subject table appears somewhere in the CSV list of IDs in the student table. Then, aggregated by subject and count the number of males and females.

SELECT
    s.SubID,
    s.SubName,
    COUNT(CASE WHEN st.Gender = 'F' THEN 1 END) Female,
    COUNT(CASE WHEN st.Gender = 'M' THEN 1 END) Male
FROM Subject s
LEFT JOIN Student st
    ON ',' + CONVERT(varchar(10), st.SubID) + ',' LIKE
       '%,' + CONVERT(varchar(10), s.SubID) + ',%'
GROUP BY
    s.SubID,
    s.SubName;

enter image description here

Demo

But, you would be best off refactoring your table design to normalize the data better. Here is an example of a student table which looks a bit better:

+---------+---------+--------+--------+
| StuID   | StuName | Gender | SubID  |
+---------+---------+--------+--------+
| 1       | David   | M      | 1      |
+---------+---------+--------+--------+
| 1       | David   | M      | 2      |
+---------+---------+--------+--------+
| 2       | Lucy    | F      | 2      |
+---------+---------+--------+--------+
| 2       | Lucy    | F      | 3      |
+---------+---------+--------+--------+

We can go a bit further, and even store the metadata separately from the StuID and SubID relationship. But even using just the above would have avoided the ugly join condition.

Upvotes: 2

Related Questions