Pivot interview questions

Question

Pivot interview questions

saravanatn

SSCarpal Tunnel

Points: 4533
More actions
March 28, 2018 at 11:28 am

#328123

Hi Everyone,
I attended a interview in top company last week . They asked me to write a query based on following scenarios
create table pivot_hcl ( empid int, eng int, mat int, science int );
insert into pivot_hcl values (101,70,25,80);
Input:
empid    eng    mat    science
101 70 25 80

I am able to get the output using hard code cross tab queries.
But I am not able to write using pivot. Any hint or help is very much appreciated.
select empid, case when empid=101 and eng=70 then 'eng' end as sub, case when empid=101 then eng end as marks from pivot_hcl union select empid, case when empid=101 and mat=25 then 'mat' end as sub, case when empid=101 then mat end as marks from pivot_hcl union select empid, case when empid=101 and science=80 then 'science' end as sub, case when empid=101 then science end as marks from pivot_hcl
Output:
empid    sub marks
101    eng 70
101    mat 25
101    science 80
Saravanan

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

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

pietlinden SSC Guru Points: 63421 More actions · Answer 1

Maybe it's me, but I think the table design they gave you (if accurate) is horrible. Subjects are facts/information, and don't really belong in column names. Would be a lot easier with a table like this:
CREATE TABLE Scores ( EmployeeID INT, Subject VARCHAR(10), Score TINYINT );

You can get the column names like this...
SELECT column_id , ac.name AS ColumnName FROM sys.all_columns ac INNER JOIN sys.all_objects ao ON ac.object_id = ao.object_id WHERE ao.name = 'pivot_hcl';

(I had to deal with this once a long time ago in Access, and it wasn't fun then either.)

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 2

pietlinden - Wednesday, March 28, 2018 11:41 AM
Maybe it's me, but I think the table design they gave you (if accurate) is horrible. Subjects are facts/information, and don't really belong in column names. Would be a lot easier with a table like this:
CREATE TABLE Scores ( EmployeeID INT, Subject VARCHAR(10), Score TINYINT );

Thanks Pietlinden. No they just provided scenarios and I created the columns data types.

Saravanan

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

This is actually an unpivot, and you could do it either with the UNPIVOT operator or with CROSS APPLY. Using the sample data you provided:

SELECT empid, sub, marks FROM pivot_hcl CROSS APPLY (VALUES('eng',eng),('mat',mat),('science',science))unpvt(sub,marks);

SELECT empid, sub, marks FROM pivot_hcl UNPIVOT (marks for sub IN (eng,mat,science))unpvt;

Cheers!

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 4

Jacob Wilkins - Wednesday, March 28, 2018 12:05 PM
This is actually an unpivot, and you could do it either with the UNPIVOT operator or with CROSS APPLY. Using the sample data you provided:
SELECT empid, sub, marks FROM pivot_hcl CROSS APPLY (VALUES('eng',eng),('mat',mat),('science',science))unpvt(sub,marks);
SELECT empid, sub, marks FROM pivot_hcl UNPIVOT (marks for sub IN (eng,mat,science))unpvt;
Cheers!

That was awesome Jacob. Thanks

Saravanan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

saravanatn - Wednesday, March 28, 2018 11:27 AM
Hi Everyone,
I attended a interview in top company last week . They asked me to write a query based on following scenarios
create table pivot_hcl ( empid int, eng int, mat int, science int );
insert into pivot_hcl values (101,70,25,80);
Input:
empid    eng    mat    science
101 70 25 80

I am able to get the output using hard code cross tab queries.
But I am not able to write using pivot. Any hint or help is very much appreciated.
select empid, case when empid=101 and eng=70 then 'eng' end as sub, case when empid=101 then eng end as marks from pivot_hcl union select empid, case when empid=101 and mat=25 then 'mat' end as sub, case when empid=101 then mat end as marks from pivot_hcl union select empid, case when empid=101 and science=80 then 'science' end as sub, case when empid=101 then science end as marks from pivot_hcl
Output:
empid    sub marks
101    eng 70
101    mat 25
101    science 80

I would have written your UNION based unpivot like this:
SELECT [empid] , 'eng' AS , [eng] AS [marks] FROM [pivot_hcl] UNION SELECT [empid] , 'mat' AS , [mat] AS [marks] FROM [pivot_hcl] UNION SELECT [empid] , 'science' AS , [science] AS [marks] FROM [pivot_hcl];

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 6

Lynn Pettis - Wednesday, March 28, 2018 2:18 PM
saravanatn - Wednesday, March 28, 2018 11:27 AM
Hi Everyone,
I attended a interview in top company last week . They asked me to write a query based on following scenarios
create table pivot_hcl ( empid int, eng int, mat int, science int );
insert into pivot_hcl values (101,70,25,80);
Input:
empid    eng    mat    science
101 70 25 80

I am able to get the output using hard code cross tab queries.
But I am not able to write using pivot. Any hint or help is very much appreciated.
select empid, case when empid=101 and eng=70 then 'eng' end as sub, case when empid=101 then eng end as marks from pivot_hcl union select empid, case when empid=101 and mat=25 then 'mat' end as sub, case when empid=101 then mat end as marks from pivot_hcl union select empid, case when empid=101 and science=80 then 'science' end as sub, case when empid=101 then science end as marks from pivot_hcl
Output:
empid    sub marks
101    eng 70
101    mat 25
101    science 80
I would have written your UNION based unpivot like this:
SELECT [empid] , 'eng' AS , [eng] AS [marks] FROM [pivot_hcl] UNION SELECT [empid] , 'mat' AS , [mat] AS [marks] FROM [pivot_hcl] UNION SELECT [empid] , 'science' AS , [science] AS [marks] FROM [pivot_hcl];

Thanks Lynn and everyone. I got different and satisfactory answers from all you.

Saravanan