Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

query help Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 9:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
hello friends

I need one request,

here is DLL

create table #temp (Student_ID char(9),
Sex char(1),
RaceCode varchar(5),
Lunch int,
Student_ESL int,
GIEP int,
IEP int,
SchoolCode int)

Insert into #temp values ('004242726','F','Black',1,0,0,0,204)
Insert into #temp values ('004242734','F','Black',0,1,0,0,356)
Insert into #temp values ('004242777','M','White',1,0,0,0,141)
Insert into #temp values ('004242793','F','Black',1,0,0,1,164)
Insert into #temp values ('004242831','M','White',0,0,0,1,141)
Insert into #temp values ('004242866','M','White',1,0,0,0,204)
Insert into #temp values ('004242882','M','White',0,1,0,0,141)
Insert into #temp values ('004242890','F','Black',0,0,0,0,204)
Insert into #temp values ('004242971','F','Black',0,0,0,1,164)
Insert into #temp values ('004243129','F','Black',0,0,0,1,204)
Insert into #temp values ('004243137','M','Black',0,0,0,0,164)
Insert into #temp values ('004243188','M','Black',1,0,0,0,204)
Insert into #temp values ('004243196','F','Other',1,1,0,0,204)
Insert into #temp values ('004243285','M','Other',0,0,0,1,204)
Insert into #temp values ('004243293','F','Other',0,0,0,1,164)
Insert into #temp values ('004243323','M','Other',0,0,0,1,204)
Insert into #temp values ('004243447','F','Black',1,1,0,0,204)
Insert into #temp values ('004243455','M','Black',0,0,0,0,164)

so the desired output should be

if we filter based on schoolcode for example 204 then output should be


BLACK WHITE OTHER
MALE 1 1 2
FEMALE 4 0 1
IEP 1 0 2
GIEP 0 0 0
LUNCH 3 1 1
ESL 1 0 1



so i need to count all my column data like sex,IEP,GIEP,LUNCH,ESL based on race code.
for IEP,GIEP,LUNCH,ESL, when the value = 1 then we need to count else not count.

I am thinking to do with pivoting and do union but if is there any easy way to solve this please let me know.
Post #1341659
Posted Tuesday, August 7, 2012 11:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1341675
Posted Tuesday, August 7, 2012 11:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:44 PM
Points: 888, Visits: 671
pls try below code

select case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END COLUMN_H,COUNT(CASE WHEN RaceCode='Black' THEN Sex END) AS Black,
COUNT(CASE WHEN RaceCode='White' THEN Sex END) AS White,
COUNT(CASE WHEN RaceCode='Other' THEN Sex END) AS Other from #temp
where SchoolCode='204'
GROUP BY (case when Sex='M' THEN 'MALE' WHEN Sex='F' THEN 'FEMALE' END)
UNION
select emp,Black,White,Other from
(SELECT RaceCode,emp,case when ord=1 then ord end ord FROM
(SELECT RaceCode,Student_ESL as ESL,GIEP,IEP,Lunch FROM #temp where SchoolCode='204')K
UNPIVOT
(ord FOR emp IN (ESL,GIEP,IEP,Lunch)
) AS pvt)k
pivot
(count(ord) FOR RaceCode IN (Black,White,Other))pvt
Post #1341680
Posted Wednesday, August 8, 2012 3:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
You can try something like this, although I don't get the same counts as you do:

;WITH Unpivoted AS (
SELECT RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
GROUP BY Code


I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341759
Posted Wednesday, August 8, 2012 3:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 23,398, Visits: 32,252
MyDoggieJessie (8/7/2012)
Not certain why you'd want to do this type of thing in SQL instead of Reporting Services or Excel (but I'm sure there's reasons ) - here's a great article that will point you in the proper direction

http://www.sqlservercentral.com/articles/Stairway+Series/87629/


Let's make this easier for others, no cut and paste required:

http://www.sqlservercentral.com/articles/Stairway+Series/87629/



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341763
Posted Wednesday, August 8, 2012 4:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 23,398, Visits: 32,252
dwain.c (8/8/2012)
You can try something like this, although I don't get the same counts as you do:

;WITH Unpivoted AS (
SELECT RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Category=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END)
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
GROUP BY Code


I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/


Because the counts shown by the OP were for a specific school code, not all the records.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341766
Posted Wednesday, August 8, 2012 4:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:

;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341771
Posted Wednesday, August 8, 2012 4:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
dwain.c (8/8/2012)
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:

;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code




Do you not like GROUP BY ROLLUP ?

e.g.
SELECT Code, BLACK, WHITE, OTHER
FROM (SELECT Code,
BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),
WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),
OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),
MAX(Pos) AS Pos
FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos
FROM #temp
CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),
('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),
('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)
) a(Code, Value, Pos)) a
WHERE SchoolCode = 204
GROUP BY ROLLUP (Code)
) a
WHERE Code IS NOT NULL
ORDER BY Pos;

Results in: -
Code   BLACK       WHITE       OTHER
------ ----------- ----------- -----------
Male 1 1 2
Female 4 0 1
IEP 1 0 2
GIEP 0 0 0
LUNCH 3 1 1
ESL 1 0 1


I've only added the ORDER BY to replicate the ordering that the OP posted.

I'll set up a big performance test in a little while to test the difference, but my gut feeling is that the GROUP BY ROLLUP will be faster.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1341780
Posted Wednesday, August 8, 2012 5:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 2,443, Visits: 7,559
Performance test as promised: -

SET NOCOUNT ON;

SELECT TOP 1000000 Student_ID,
Sex = CASE WHEN sex = 1 THEN 'M' ELSE 'F' END,
RaceCode = CASE race WHEN 0 THEN 'Other' WHEN 1 THEN 'Black' ELSE 'White' END,
Lunch = lunch, Student_ESL = esl, GIEP = giep, IEP = iep,
SchoolCode = schoolcode
INTO #temp
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3
CROSS APPLY (SELECT DISTINCT LEFT('0' + (CAST((ABS(CHECKSUM(NEWID())) % 999999999) + 1 AS VARCHAR(9))),9)) sc4(Student_ID)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc5(sex)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 3)) sc6(race)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc7(lunch)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc8(esl)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc9(giep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc10(iep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 899) + 100) sc11(schoolcode);

CREATE CLUSTERED INDEX PK_Student_ID_temp ON #temp (Student_ID);
CREATE NONCLUSTERED INDEX NC_SchoolCode_temp ON #temp (SchoolCode);

PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;

;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code

SET STATISTICS IO, TIME OFF;

PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;

SELECT Code, BLACK, WHITE, OTHER
FROM (SELECT Code,
BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),
WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),
OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),
MAX(Pos) AS Pos
FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos
FROM #temp
CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),
('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),
('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)
) a(Code, Value, Pos)
) a
WHERE SchoolCode = 204
GROUP BY ROLLUP (Code)
) a
WHERE Code IS NOT NULL
ORDER BY Pos;

SET STATISTICS IO, TIME OFF;

Results: -
================================================================================
DWAIN
================================================================================
Sex BLACK WHITE OTHER
------ ----------- ----------- -----------
FEMALE 197 177 171
MALE 164 199 191
LUNCH 178 177 178
GIEP 193 187 179
IEP 184 189 188
ESL 180 186 178
Warning: Null value is eliminated by an aggregate or other SET operation.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 2, logical reads 6762, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 20 ms.

================================================================================
CADAVRE
================================================================================
Code BLACK WHITE OTHER
------ ----------- ----------- -----------
Male 164 199 191
Female 197 177 171
IEP 184 189 188
GIEP 193 187 179
LUNCH 178 177 178
ESL 180 186 178

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 1, logical reads 3381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 16 ms.


I'm a bit surprised, seems the results are comparable. So I guess it's down to personal preference.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1341790
Posted Wednesday, August 8, 2012 5:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
Cadavre (8/8/2012)

Do you not like GROUP BY ROLLUP ?



Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.

The performance test result is interesting.

Try mine with:

OPTION (MAXDOP 2)

And watch the CPU result level out to yours. I'm still trying to figure that one out.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341798
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse