Converting multiple rows to many columns using pivot

Question

Post reply

Converting multiple rows to many columns using pivot

netguykb

Ten Centuries

Points: 1310

January 14, 2019 at 9:10 am

#379020

I would like to use PIVOT for converting multiple rows into multiple columns.

Here is the current output based on the data stored: (Note the SQ for creating this table is located at the very bottom of the post after the various SCRIPTS for creating tables and data.

competency_name

competency_id

proficiency_description

proficiency_level_name

proficiency_level_value

Generating insights

24

Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.

Awareness

1

Generating insights

24

Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.

Basic

2

Generating insights

24

Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.

Intermediate

3

Generating insights

24

Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.

Advanced

4

Generating insights

24

Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights

Expert

5

I would like the following:

competency_name

competency_id

Awareness

Basic

Intermediate

Advanced

Expert

Generating insights

24

Determines why information gathered is important and what impact it will have on. Uses software to illustrate insights visually.

Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.

Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have.

Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have. Able to illustrate, analyze and synthesize information in order to meet strategic goals.

Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights

Here are the scripts for creating the data:

dbo.Swoz_xTrack_Type_Competency_test
dbo.Swoz_Competency_test
dbo.Swoz_Proficiency_test
dbo.Swoz_Proficiency_Level_test

CREATE TABLE dbo.Swoz_xTrack_Type_Competency_test ( track_id int not null, type_id int not null, competency_id int not null, client_id int not null, competency_status nvarchar(8) DEFAULT 'ACTIVE' );

INSERT INTO dbo.Swoz_xTrack_Type_Competency_test (track_id,type_id,competency_id,client_id) VALUES (1,1,24,1)

CREATE TABLE dbo.Swoz_Competency_test ( competency_id int PRIMARY KEY, client_id int not null, competency_name text not null, competency_description text, competency_status nvarchar(8) DEFAULT 'ACTIVE' );

INSERT INTO dbo.Swoz_Competency_test (competency_id,client_id,competency_name) VALUES (20,1,'Assessing and Selecting Leaders'), (21,1,'Big Data'), (22,1,'Brand Development'), (23,1,'Campaign Management'), (24,1,'Generating insights')

CREATE TABLE dbo.Swoz_Proficiency_test ( proficiency_id int IDENTITY(1,1) PRIMARY KEY, client_id int NOT NULL, competency_id int NOT NULL, proficiency_description text NULL, proficiency_level_id int NOT NULL, proficiency_status nvarchar(8) NULL DEFAULT ('ACTIVE')

);

INSERT INTO dbo.Swoz_Proficiency_test(client_id, competency_id, proficiency_level_id, proficiency_description) VALUES

------------------------------------- START ANALYSIS --------------------------------------------------

-- Type Analysis (type_id = 1), Competency Generating Insights (competency_id = 24) (1,24,1,'Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.'), -- proficiency_level_id = 1 (1,24,2,'Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.'),-- proficiency_level_id = 2 (1,24,3,'Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.'),-- proficiency_level_id = 3 (1,24,4,'Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.'),-- proficiency_level_id = 4 (1,24,5,'Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights')-- proficiency_level_id = 5

CREATE TABLE dbo.Swoz_Proficiency_Level_test ( proficiency_level_id int IDENTITY(1,1) PRIMARY KEY, client_id int not null, proficiency_level_name nvarchar(30) not null, proficiency_level_value int not null, proficiency_level_status nvarchar(8) DEFAULT 'ACTIVE' );

INSERT INTO dbo.Swoz_Proficiency_Level_test (client_id, proficiency_level_name, proficiency_level_value) VALUES (1,'Awareness',1), (1,'Basic',2), (1,'Intermediate',3), (1,'Advanced',4), (1,'Expert',5)

SELECT D.competency_name, D.competency_id, F.proficiency_description, G.proficiency_level_name, G.proficiency_level_value

FROM dbo.Swoz_xTrack_Type_Competency_test A

INNER JOIN dbo.Swoz_Competency_test D ON A.competency_id = D.competency_id

LEFT JOIN dbo.Swoz_Proficiency_test F ON D.competency_id = F.competency_id

LEFT JOIN dbo.Swoz_Proficiency_Level_test G ON F.proficiency_level_id = G.proficiency_level_id

WHERE A.competency_status = 'ACTIVE' AND A.track_id = 1 AND A.type_id = 1 AND D.competency_id = 24

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

netguykb - Monday, January 14, 2019 9:10 AM
I would like to use PIVOT for converting multiple rows into multiple columns.
Here is the current output based on the data stored: (Note the SQ for creating this table is located at the very bottom of the post after the various SCRIPTS for creating tables and data.
competency_name
competency_id
proficiency_description
proficiency_level_name
proficiency_level_value
Generating insights
24
Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.
Awareness
1
Generating insights
24
Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.
Basic
2
Generating insights
24
Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.
Intermediate
3
Generating insights
24
Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.
Advanced
4
Generating insights
24
Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights
Expert
5
I would like the following:
competency_name
competency_id
Awareness
Basic
Intermediate
Advanced
Expert
Generating insights
24
Determines why information gathered is important and what impact it will have on. Uses software to illustrate insights visually.
Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.
Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have.
Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have. Able to illustrate, analyze and synthesize information in order to meet strategic goals.
Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights
Here are the scripts for creating the data:
dbo.Swoz_xTrack_Type_Competency_test
dbo.Swoz_Competency_test
dbo.Swoz_Proficiency_test
dbo.Swoz_Proficiency_Level_test
CREATE TABLE dbo.Swoz_xTrack_Type_Competency_test ( track_id int not null, type_id int not null, competency_id int not null, client_id int not null, competency_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_xTrack_Type_Competency_test (track_id,type_id,competency_id,client_id) VALUES (1,1,24,1)
CREATE TABLE dbo.Swoz_Competency_test ( competency_id int PRIMARY KEY, client_id int not null, competency_name text not null, competency_description text, competency_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_Competency_test (competency_id,client_id,competency_name) VALUES (20,1,'Assessing and Selecting Leaders'), (21,1,'Big Data'), (22,1,'Brand Development'), (23,1,'Campaign Management'), (24,1,'Generating insights')
CREATE TABLE dbo.Swoz_Proficiency_test ( proficiency_id int IDENTITY(1,1) PRIMARY KEY, client_id int NOT NULL, competency_id int NOT NULL, proficiency_description text NULL, proficiency_level_id int NOT NULL, proficiency_status nvarchar(8) NULL DEFAULT ('ACTIVE')
);
INSERT INTO dbo.Swoz_Proficiency_test(client_id, competency_id, proficiency_level_id, proficiency_description) VALUES
------------------------------------- START ANALYSIS --------------------------------------------------
-- Type Analysis (type_id = 1), Competency Generating Insights (competency_id = 24) (1,24,1,'Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.'), -- proficiency_level_id = 1 (1,24,2,'Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.'),-- proficiency_level_id = 2 (1,24,3,'Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.'),-- proficiency_level_id = 3 (1,24,4,'Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.'),-- proficiency_level_id = 4 (1,24,5,'Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights')-- proficiency_level_id = 5
CREATE TABLE dbo.Swoz_Proficiency_Level_test ( proficiency_level_id int IDENTITY(1,1) PRIMARY KEY, client_id int not null, proficiency_level_name nvarchar(30) not null, proficiency_level_value int not null, proficiency_level_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_Proficiency_Level_test (client_id, proficiency_level_name, proficiency_level_value) VALUES (1,'Awareness',1), (1,'Basic',2), (1,'Intermediate',3), (1,'Advanced',4), (1,'Expert',5)
SELECT D.competency_name, D.competency_id, F.proficiency_description, G.proficiency_level_name, G.proficiency_level_value
FROM dbo.Swoz_xTrack_Type_Competency_test A
INNER JOIN dbo.Swoz_Competency_test D ON A.competency_id = D.competency_id
LEFT JOIN dbo.Swoz_Proficiency_test F ON D.competency_id = F.competency_id
LEFT JOIN dbo.Swoz_Proficiency_Level_test G ON F.proficiency_level_id = G.proficiency_level_id
WHERE A.competency_status = 'ACTIVE' AND A.track_id = 1 AND A.type_id = 1 AND D.competency_id = 24

Before taking the time to work this would you please post the code you have tried writing to solve this problem and what issues you are experiencing.

netguykb Ten Centuries Points: 1310 More actions · Answer 2

Lynn Pettis - Monday, January 14, 2019 9:22 AM
netguykb - Monday, January 14, 2019 9:10 AM
I would like to use PIVOT for converting multiple rows into multiple columns.
Here is the current output based on the data stored: (Note the SQ for creating this table is located at the very bottom of the post after the various SCRIPTS for creating tables and data.
competency_name
competency_id
proficiency_description
proficiency_level_name
proficiency_level_value
Generating insights
24
Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.
Awareness
1
Generating insights
24
Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.
Basic
2
Generating insights
24
Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.
Intermediate
3
Generating insights
24
Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.
Advanced
4
Generating insights
24
Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights
Expert
5
I would like the following:
competency_name
competency_id
Awareness
Basic
Intermediate
Advanced
Expert
Generating insights
24
Determines why information gathered is important and what impact it will have on. Uses software to illustrate insights visually.
Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.
Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have.
Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have. Able to illustrate, analyze and synthesize information in order to meet strategic goals.
Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights
Here are the scripts for creating the data:
dbo.Swoz_xTrack_Type_Competency_test
dbo.Swoz_Competency_test
dbo.Swoz_Proficiency_test
dbo.Swoz_Proficiency_Level_test
CREATE TABLE dbo.Swoz_xTrack_Type_Competency_test ( track_id int not null, type_id int not null, competency_id int not null, client_id int not null, competency_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_xTrack_Type_Competency_test (track_id,type_id,competency_id,client_id) VALUES (1,1,24,1)
CREATE TABLE dbo.Swoz_Competency_test ( competency_id int PRIMARY KEY, client_id int not null, competency_name text not null, competency_description text, competency_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_Competency_test (competency_id,client_id,competency_name) VALUES (20,1,'Assessing and Selecting Leaders'), (21,1,'Big Data'), (22,1,'Brand Development'), (23,1,'Campaign Management'), (24,1,'Generating insights')
CREATE TABLE dbo.Swoz_Proficiency_test ( proficiency_id int IDENTITY(1,1) PRIMARY KEY, client_id int NOT NULL, competency_id int NOT NULL, proficiency_description text NULL, proficiency_level_id int NOT NULL, proficiency_status nvarchar(8) NULL DEFAULT ('ACTIVE')
);
INSERT INTO dbo.Swoz_Proficiency_test(client_id, competency_id, proficiency_level_id, proficiency_description) VALUES
------------------------------------- START ANALYSIS --------------------------------------------------
-- Type Analysis (type_id = 1), Competency Generating Insights (competency_id = 24) (1,24,1,'Determines why information gathered is important and what impact it will have on Rollins. Uses software to illustrate insights visually.'), -- proficiency_level_id = 1 (1,24,2,'Produces quality analysis, identifying key findings that represent potential opportunities or challenges. Synthesizes key findings to derive the implications to Rollins. Determines why information gathered is important and what impact it will have on Rollins. Effectively uses software to illustrate insights visually.'),-- proficiency_level_id = 2 (1,24,3,'Identifies key findings that represent potential opportunities or challenges.Responds questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins.'),-- proficiency_level_id = 3 (1,24,4,'Predicts potential questions as a result of the findings and determines appropriate next steps. Uses past experience and broad knowledge to consistently identify what implications the key findings will have on Rollins. Able to illustrate, analyze and synthesize information in order to meet strategic goals.'),-- proficiency_level_id = 4 (1,24,5,'Takes action, when appropriate, to involve other groups. Trains others on methods for generating insights')-- proficiency_level_id = 5
CREATE TABLE dbo.Swoz_Proficiency_Level_test ( proficiency_level_id int IDENTITY(1,1) PRIMARY KEY, client_id int not null, proficiency_level_name nvarchar(30) not null, proficiency_level_value int not null, proficiency_level_status nvarchar(8) DEFAULT 'ACTIVE' );
INSERT INTO dbo.Swoz_Proficiency_Level_test (client_id, proficiency_level_name, proficiency_level_value) VALUES (1,'Awareness',1), (1,'Basic',2), (1,'Intermediate',3), (1,'Advanced',4), (1,'Expert',5)
SELECT D.competency_name, D.competency_id, F.proficiency_description, G.proficiency_level_name, G.proficiency_level_value
FROM dbo.Swoz_xTrack_Type_Competency_test A
INNER JOIN dbo.Swoz_Competency_test D ON A.competency_id = D.competency_id
LEFT JOIN dbo.Swoz_Proficiency_test F ON D.competency_id = F.competency_id
LEFT JOIN dbo.Swoz_Proficiency_Level_test G ON F.proficiency_level_id = G.proficiency_level_id
WHERE A.competency_status = 'ACTIVE' AND A.track_id = 1 AND A.type_id = 1 AND D.competency_id = 24
Before taking the time to work this would you please post the code you have tried writing to solve this problem and what issues you are experiencing.

Lynn,

I have no code that support the ask. I have posted this to see if I could get some assistance with the end code necessary for displaying the data based on the second table in this post. I have never created code for converting row data into column data. I have done some research about PIVOT but in many of the examples the rows being converted to column are showing examples of sum or averaging data. Tried to reverse engineer it however still have no luck. Heres the URL for that example.

https://dzone.com/articles/pivoting-and-unpivoting-multiple-columns-in-ms-sql

netguykb Ten Centuries Points: 1310 More actions · Answer 3

Thank you I have asked for help and have an answer that I thought I would post:

--------------- -- SQL Method -- ---------------- SELECT D.competency_name ,D.competency_id ,MAX(CASE WHEN G.proficiency_level_name = 'Awareness' THEN F.proficiency_description END) AS [Awareness] ,MAX(CASE WHEN G.proficiency_level_name = 'Basic' THEN F.proficiency_description END) AS [Basic] ,MAX(CASE WHEN G.proficiency_level_name = 'Intermediate' THEN F.proficiency_description END) AS [Intermediate] ,MAX(CASE WHEN G.proficiency_level_name = 'Advanced' THEN F.proficiency_description END) AS [Advanced] ,MAX(CASE WHEN G.proficiency_level_name = 'Expert' THEN F.proficiency_description END) AS [Expert] FROM dbo.Swoz_xTrack_Type_Competency_test A INNER JOIN dbo.Swoz_Competency_test D ON A.competency_id = D.competency_id LEFT JOIN dbo.Swoz_Proficiency_test F ON D.competency_id = F.competency_id LEFT JOIN dbo.Swoz_Proficiency_Level_test G ON F.proficiency_level_id = G.proficiency_level_id WHERE A.competency_status = 'ACTIVE' AND A.track_id = 1 AND A.type_id = 1 AND D.competency_id = 24 GROUP BY D.competency_name, D.competency_id

------------------ -- PIVOT Method -- ------------------ SELECT competency_name ,competency_id ,[Awareness] ,[Basic] ,[Intermediate] ,[Advanced] ,[Expert] FROM ( SELECT D.competency_name ,D.competency_id ,G.proficiency_level_name ,F.proficiency_description FROM dbo.Swoz_xTrack_Type_Competency_test A INNER JOIN dbo.Swoz_Competency_test D ON A.competency_id = D.competency_id LEFT JOIN dbo.Swoz_Proficiency_test F ON D.competency_id = F.competency_id LEFT JOIN dbo.Swoz_Proficiency_Level_test G ON F.proficiency_level_id = G.proficiency_level_id WHERE A.competency_status = 'ACTIVE' AND A.track_id = 1 AND A.type_id = 1 AND D.competency_id = 24 ) Sub PIVOT ( MAX(proficiency_description) FOR proficiency_level_name IN ([Awareness] ,[Basic] ,[Intermediate] ,[Advanced] ,[Expert] ) ) PVT