July 12, 2012 at 7:44 am
Folks:
I have data in this format in a table with Employee Name and Application Access (PACcess, HAccess, CAccess)
CREATE TABLE #Temp1
(EmpName varchar(50),
PAccess varchar(2),
HAccess varchar(2),
CAccess varchar(2))
INSERT INTO #Temp1 values ('John D','0','1','1')
INSERT INTO #Temp1 values ('Chris H','0','0','1')
INSERT INTO #Temp1 values ('Mary G','1','0','0')
INSERT INTO #Temp1 values ('Tom H','1','1','0')
INSERT INTO #Temp1 values ('Tim V','0','0','1')
INSERT INTO #Temp1 values ('Jenn T','0','1','0')
INSERT INTO #Temp1 values ('Paul H','1','1','1')
SELECT * FROM #Temp1
Where '0' indicates 'Doesn't have Access' and '1' indicates 'Has the Access'.
I would like the output to be Grouped by Application which has the Access: The output should be:
PAccessMary G
PAccessTom H
PAccessPaul H
HAccessJohn D
HAccessTom H
HAccessJenn T
HAccessPaul H
CAccessJohn D
CAccessChris H
CAccessTim V
CAccessPaul H
How would be my SQL Query be?
Thanks !
July 12, 2012 at 7:57 am
Select each one separately, then use the Union All operator to stack them.
Something like:
select 1 as Seq, 'PAccess' as AppAccess, Name
from dbo.MyTable
where PAccess = 1
Union All
select 2, 'HAccess', Name
from dbo.MyTable
where HAccess = 1
Union All
select 3, 'CAccess', Name
from dbo.MyTable
where CAccess = 1
Order By Seq, Name;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 8:08 am
Or UNPIVOT
CREATE TABLE #Temp1
(EmpName varchar(50),
PAccess varchar(2),
HAccess varchar(2),
CAccess varchar(2))
INSERT INTO #Temp1 values ('John D','0','1','1')
INSERT INTO #Temp1 values ('Chris H','0','0','1')
INSERT INTO #Temp1 values ('Mary G','1','0','0')
INSERT INTO #Temp1 values ('Tom H','1','1','0')
INSERT INTO #Temp1 values ('Tim V','0','0','1')
INSERT INTO #Temp1 values ('Jenn T','0','1','0')
INSERT INTO #Temp1 values ('Paul H','1','1','1')
SELECT *
FROM
( SELECT * FROM #Temp1 ) AS A
UNPIVOT
(
SystemAccess FOR System IN ([PAccess],[HAccess],[CAccess])
) AS UPivot
WHERE
SystemAccess = 1
ORDER BY
System desc
drop table #Temp1
Edit: Added Order By
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply