March 28, 2018 at 11:28 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
Saravanan
March 28, 2018 at 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 );
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.)
March 28, 2018 at 11:50 am
pietlinden - Wednesday, March 28, 2018 11:41 AMMaybe 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
March 28, 2018 at 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!
March 28, 2018 at 12:14 pm
Jacob Wilkins - Wednesday, March 28, 2018 12:05 PMThis 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
March 28, 2018 at 2:18 pm
saravanatn - Wednesday, March 28, 2018 11:27 AMHi 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_hclOutput:
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];
March 28, 2018 at 10:18 pm
Lynn Pettis - Wednesday, March 28, 2018 2:18 PMsaravanatn - Wednesday, March 28, 2018 11:27 AMHi 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_hclOutput:
empid sub marks
101 eng 70
101 mat 25
101 science 80I 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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy