SQL Query Help with roll up

Transact-SQL
1

Hello all

I have the following Code:

SELECT        TOP (10) PERCENT dbo.EMP.SSNO AS Emp_ID, dbo.BADGE.ID AS Card_Number, dbo.EMP.LASTNAME AS Last_Name, dbo.EMP.FIRSTNAME AS First_Name, dbo.EMP.MIDNAME AS Middle_Name,

                         dbo.BADGE.STATUS AS Card_Status, dbo.BADGE.TYPE AS Badge_Type, dbo.ACCESSLVL.DESCRIPT AS Access_Level

FROM            dbo.BADGELINK INNER JOIN

                         dbo.ACCESSLVL ON dbo.BADGELINK.ACCLVLID = dbo.ACCESSLVL.ACCESSLVID RIGHT OUTER JOIN

                         dbo.BADGE ON dbo.BADGELINK.BADGEKEY = dbo.BADGE.BADGEKEY LEFT OUTER JOIN

                         dbo.EMP LEFT OUTER JOIN

                         dbo.UDFEMP ON dbo.EMP.ID = dbo.UDFEMP.ID ON dbo.BADGE.EMPID = dbo.EMP.ID

WHERE        (dbo.BADGE.STATUS = 1) AND (dbo.BADGE.TYPE = 11 OR

                        dbo.BADGE.TYPE = 12)

ORDER BY Card_Number

heres the problem...

it does everything its supposed to but the Last Column...

the Card_Numbers are Unique its the Access_Level...

example..

Card_number Access_Level
1234 Entry
1234 Exit
1234 Rear
1234 Stairs

i need all the information above in the query like:
Emp_ID Card_Number Last_Name First_Name Middle_Name Card_Status Badge_Type Access_Level

but it need to look like (this could potentially contain 16 Access_Levels.

all the headers above with
Emp_ID Card_Number Last_Name First_Name Middle_Name Card_Status Badge_Type Access_level Access_level1 Access_level2 Access_level3 etc
1234 Entry Exit Rear Stairs

I hoipe this makes sense

2

Please post create table DDL, inserts of sample data and desired output. Thanks for providing what you've tried.

3

LOL...

Id love to...i dont know what your asking...kinda new at this.

4

Ok...i made an extremepy Small workbook..

https://www.dropbox.com/s/pytpkqoxn4ihj7k/Show.xlsx?dl=0
5

Hi

Create Data Script
.. I have made some changes .. I put only 1 column name instead of last name first name etc
.. also in EMPID i removed M in 100543M

Create Data 
drop table #data
go 

create table #data
(Emp_ID	int null,Card_Number	int null,Name varchar(100) null,
Card_Status	int null,Badge_Type	int null,Access_Level varchar(200) null)
go 

insert into #data select 100543,	23769,	'McLaughlin 	Mark		',1	,12	 ,'Global - Grand Master - Executives - SAS MGR APPROVAL REQUIRED'
go 
insert into #data select 108232,	31335,	'Harris	Jennifer		',1,	12	 ,'Global - Company Employee ("Employees" only)	'
go 
insert into #data select 108546,	31380,	'Uyehara	Kim		',1	,12	 ,'Global - Company Employee ("Employees" only)H4	'
go 
insert into #data select 108546,	31380,	'Uyehara	Kim		',1	,12	,'PLA.1.1 Executive Conference Room	'
go

select * from #data 
go

This SQL has limitations .. if the data is different from what you posted in dropbox ..if data different SQL needs to be changed ..i mean logic ..

SQL 
SELECT emp_id, 
       card_number, 
       NAME, 
       card_status, 
       badge_type, 
       Min(access_level), 
       CASE 
         WHEN Min(access_level) <> Max(access_level) THEN Max(access_level) 
         ELSE NULL 
       END 
FROM   #data 
GROUP  BY emp_id, 
          card_number, 
          NAME, 
          card_status, 
          badge_type
6 
SELECT        TOP (10) PERCENT dbo.EMP.SSNO AS Emp_ID, dbo.BADGE.ID AS Card_Number, dbo.EMP.LASTNAME AS Last_Name, dbo.EMP.FIRSTNAME AS First_Name, dbo.EMP.MIDNAME AS Middle_Name,

                         dbo.BADGE.STATUS AS Card_Status, dbo.BADGE.TYPE AS Badge_Type, dbo.ACCESSLVL.DESCRIPT AS Access_Level

FROM            dbo.BADGELINK INNER JOIN

                         dbo.ACCESSLVL ON dbo.BADGELINK.ACCLVLID = dbo.ACCESSLVL.ACCESSLVID RIGHT OUTER JOIN

                         dbo.BADGE ON dbo.BADGELINK.BADGEKEY = dbo.BADGE.BADGEKEY LEFT OUTER JOIN

                         dbo.EMP LEFT OUTER JOIN

                         dbo.UDFEMP ON dbo.EMP.ID = dbo.UDFEMP.ID ON dbo.BADGE.EMPID = dbo.EMP.ID

WHERE        (dbo.BADGE.STATUS = 1) AND (dbo.BADGE.TYPE = 11 OR

                        dbo.BADGE.TYPE = 12)

ORDER BY Card_Number

Ok maybe i wasnt Clear..

This is a query That when ran creates thousands of records... the sample i showed you was a snippit of what the end result looks like.. When i run the query i need it to look like in the sample excel work book using the Query Script above

7

There may be users that have 1, 2, 5, thru 16 Access Levels..

User 1 could have one...
User 2 Could have 5,
User 2 Could have 3.

Because the Users have more than one access level, its duplicating them, but the access level name is changing..

example in CSV format..

emp_ID,card#,Last_Name,First_name, Middle,card_status,badgetype,access_level
2332312,2332,vader,darth,null,1,12,Front Access
1223,4456,skywalker,luke,null,1,12,Front Access
1223,4456,skywalker,luke,null,1,12,Rear Access
57666,2332,solo,han,null,1,12,Front Access
57666,2332,solo,han,null,1,12,Rear Access
57666,2332,solo,han,null,1,12,Side Access

This is what the query exports now as a sample....

what i need it the query to run this way...
emp_ID,card#,Last_Name,First_name, Middle,card_status,badgetype,access_level,access_level2,access_level3,access_level4 etc up to 16
2332312,2332,vader,darth,null,1,12,Front access
1223,4456,skywalker,luke,null,1,12,Front Access,Rear Access
57666,2332,solo,han,null,1,12,Front Access,Rear Access,Side Access

Please note these are not the access level names, im just illustrating what its doing, and what how i need it to work

8

It sounds like you are looking for a way to pivot the data - here is one solution:

Declare @data Table (
        Emp_ID int Null
      , Card_Number int Null
      , LastName varchar(35) Null
      , FirstName varchar(35) Null
      , Card_Status int Null
      , Badge_Type int Null
      , Access_Level varchar(200) null
        );

 Insert Into @data (Emp_ID, Card_Number, LastName, FirstName, Card_Status, Badge_Type, Access_Level)
 Values (100543, 23769,	'McLaughlin', 'Mark', 1, 12, 'Front Access')
      , (108232, 31335,	'Harris', 'Jennifer', 1, 12, 'Rear Access')
      , (108546, 31380,	'Uyehara', 'Kim', 1, 12, 'Front Access')
      , (108546, 31380, 'Uyehara', 'Kim', 1, 12, 'Side Access')
      , (108546, 31380, 'Uyehara', 'Kim', 1, 12, 'Rear Access');

   With accessLevels
     As (
 Select d.Emp_ID
      , d.Card_Number
      , d.LastName
      , d.FirstName
      , d.Card_Status
      , d.Badge_Type
      , d.Access_Level
      , AccessLevelNumber = row_number() over(Partition By d.Emp_ID Order By d.Access_Level)
   From @data d
        )
 Select al.Emp_ID
      , al.Card_Number
      , al.LastName
      , al.FirstName
      , al.Card_Status
      , al.Badge_Type
      , AccessLevel1 = max(Case When al.AccessLevelNumber = 1 Then al.Access_Level End)
      , AccessLevel2 = max(Case When al.AccessLevelNumber = 2 Then al.Access_Level End)
      , AccessLevel3 = max(Case When al.AccessLevelNumber = 3 Then al.Access_Level End)
   From accessLevels    al
  Group By
        al.Emp_ID
      , al.Card_Number
      , al.LastName
      , al.FirstName
      , al.Card_Status
      , al.Badge_Type;

We can pivot each access level to a specified column by assigning each access level a number for that employee. So if an employee only has 2 access levels defined - the first 2 columns would be populated, if they only have one - the first column will be populated. If they have all 16 - then all 16 columns would be populated (you have to add those columns).