Regard Generate output for report

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

You can use the CASE function for this. I dont have SQL installed in m/c now to give u the exact query.

"Keep Trying"

Paul White SSC Guru Points: 150467 More actions · Answer 2

DECLARE @data TABLE (test_id INT PRIMARY KEY, gender TINYINT NOT NULL CHECK (gender BETWEEN 1 AND 2));

INSERT @data VALUES (1,1), (2,1), (3,2);

SELECT

test_id,

male = CONVERT(BIT, gender - 2),

female = CONVERT(BIT, gender - 1)

FROM

kiranbgiet SSC Journeyman Points: 83 More actions · Answer 3

Thanks

for Your Immediate Response

But TestId is not unique ,number of male can give same test ,then

DECLARE @data TABLE (test_id INT , gender TINYINT NOT NULL CHECK (gender BETWEEN 1 AND 2));

INSERT @data VALUES (1,1), (2,1), (3,2),(1,1) ;

SELECT

test_id,

male = CONVERT(BIT, gender - 2),

female = CONVERT(BIT, gender - 1)

FROM

in this case i want output like

testid male female

1 2 0

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

Both gives you correct direction for what you post first, but now you needs, the id repeated and counts the genders.

DECLARE @data TABLE

(test_id INT ,

gender INT )

INSERT @data VALUES (1,1)

INSERT @data VALUES (2,1)

INSERT @data VALUES (3,2)

INSERT @data VALUES (1,1)

select test_id,(case when gender = 1 then count(gender)else 0 end)[Male],

(case when gender = 2 then count(gender)else 0 end)[Female]

from @data

group by test_id,gender

Paul White SSC Guru Points: 150467 More actions · Answer 5

Please try to be more clear in your first post next time. Thank you.