Pivot.unpivot

Transact-SQL
1

I WOULD LIKE TO PIVOT/ UNPIVOT THE FOLLOWING TABLE

ORIGINAL TABLE

Name, QTR,Contact_percentage,building_percentage,insurance_percentage,medical_percentage
JANE, 1, 23%,56%,65%,34%
JANE 2, 45%,56%,34%,76%
JANE 3, 32%,67%,54%,87%
JANE 4, 78%,63%,35%,26%
JOHN, 1, 53%,54%,87%,19%
JOHN, 2, 96%,79%,70%,71%
JOHN ,3, 61%,68%,14%,91%
JOHN 4, 9%,54%,87%,65%

FROM TABLE dbo.test1

EXPECTED RESULT

NAME , SELECTION, QTR1, QTR2, QTR3, QTR4
JANE Contact_percentage, 23 %,45%,32%,78%
JANE building_percentage , 56%,56%,67%,63%
JANE insurance_percentage, 65%,34%,54%,35%
JANE medical_percentage, 34%,76%,87%,26%
JOHN Contact_percentage, 53%,96%,61%,9%
JOHN building_percentage , 54%,79%,68%,54%
JOHN insurance_percentage, 87%,34%,14%,87%
JOHN medical_percentage, 53%,79%,91%,65%
,

PLEASE ADVISE

2

Please post create table with inserts script

3

create table test1
(
name varchar(100) null,
qtr int null,
Contact_percentage int,
building_percentage int,
insurance_percentage int,
medical_percentage int )

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JANE,' 1, 23,56,65,34);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JANE,' 1, 45,56,34,76);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JANE,' 3, 32,67,54,87);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JANE,' 4, 78,63,35,26);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JOHN,' 1, 53,54,87,19);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JOHN,' 1, 96,79,70,71);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JOHN,' 3, 61,68,14,91);

insert into test1 (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
values ('JOHN,' 4, 9,54,87,65);

4 
DROP TABLE IF EXISTS #test;
CREATE table #test
(
name varchar(100) null,
qtr int null,
Contact_percentage int,
building_percentage int,
insurance_percentage int,
medical_percentage int );
insert into #test (name,qtr,contact_percentage,building_percentage,insurance_percentage,medical_percentage)
VALUES ('JANE', 1, 23,56,65,34)
     , ('JANE', 2, 45,56,34,76)
     , ('JANE', 3, 32,67,54,87)
     , ('JANE', 4, 78,63,35,26)
     , ('JOHN', 1, 53,54,87,19)
     , ('JOHN', 2, 96,79,70,71)
     , ('JOHN', 3, 61,68,14,91)
     , ('JOHN', 4, 9,54,87,65);
WITH cp AS
(
SELECT NAME
     , 'contact_percentage' SELECTION
     , [1] AS QTR1
     , [2] AS QTR2
     , [3] AS QTR3
     , [4] AS QTR4
FROM #test
PIVOT (Max(Contact_percentage) FOR qtr IN([1], [2], [3], [4])) P ),
     bp AS
(
SELECT NAME
     , 'building_percentage' SELECTION
     , [1] AS QTR1
     , [2] AS QTR2
     , [3] AS QTR3
     , [4] AS QTR4
FROM #test
PIVOT (Max(building_percentage) FOR qtr IN([1], [2], [3], [4])) P),    
     [ip] AS
(
SELECT NAME
     , 'insurance_percentage' SELECTION
     , [1] AS QTR1
     , [2] AS QTR2
     , [3] AS QTR3
     , [4] AS QTR4
FROM #test
PIVOT (Max(insurance_percentage) FOR qtr IN([1], [2], [3], [4])) P),
     mp AS
(
SELECT NAME
     , 'medical_percentage' SELECTION
     , [1] AS QTR1
     , [2] AS QTR2
     , [3] AS QTR3
     , [4] AS QTR4
FROM #test
PIVOT (Max(medical_percentage) FOR qtr IN([1], [2], [3], [4])) P)    
SELECT NAME, SELECTION
     , Cast(SUM(QTR1) AS varchar(10))+'%' AS QTR1 
     , Cast(SUM(QTR2) AS varchar(10))+'%' AS QTR2 
     , Cast(SUM(QTR3) AS varchar(10))+'%' AS QTR3 
     , Cast(SUM(QTR4) AS varchar(10))+'%' AS QTR4 
FROM cp
GROUP BY name, selection   
UNION
SELECT name, SELECTION
     , Cast(SUM(QTR1) AS varchar(10))+'%' AS QTR1 
     , Cast(SUM(QTR2) AS varchar(10))+'%' AS QTR2 
     , Cast(SUM(QTR3) AS varchar(10))+'%' AS QTR3 
     , Cast(SUM(QTR4) AS varchar(10))+'%' AS QTR4 
FROM bp
GROUP BY name, selection   
UNION
SELECT name, SELECTION
     , Cast(SUM(QTR1) AS varchar(10))+'%' AS QTR1 
     , Cast(SUM(QTR2) AS varchar(10))+'%' AS QTR2 
     , Cast(SUM(QTR3) AS varchar(10))+'%' AS QTR3 
     , Cast(SUM(QTR4) AS varchar(10))+'%' AS QTR4 
FROM [ip]
GROUP BY name, selection   
UNION
SELECT name, SELECTION
     , Cast(SUM(QTR1) AS varchar(10))+'%' AS QTR1 
     , Cast(SUM(QTR2) AS varchar(10))+'%' AS QTR2 
     , Cast(SUM(QTR3) AS varchar(10))+'%' AS QTR3 
     , Cast(SUM(QTR4) AS varchar(10))+'%' AS QTR4 
FROM mp
GROUP BY name, selection;   

DROP TABLE #test;

image

1 Like
5

thank you sir will test it out and advise