Using Pivot to covnert rows to columns

Question

Hi All

I am importing data from a source database which happens to column data in rows. please see the illustration below.

Custom fields

Jira data

In the example above, I understand that I can use the pivot functionality in SQL server to achieve thesame result .

select A.*, (select cf.date_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Start Date') Start_Date,  
(select cf.number_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Story Points') Story_Points,  
(select cf.custom_value from v_jira_custom_fields cf where cf.issue_id = a.issue_id and cf.custom_field_name = 'Ready') Ready  
from jira_data A  
where A.project = 'DAK'  
and A.issue_id = 2222  

In effect, where the join matches for Start_Date, select the date value for the start date custom field if it exists as transform into a column.
This applies to the others, story point and ready.

The end goal is this

Answer 1

Hi @MrFlinstone ,

Welcome to Microsoft Q&A!

Please also refer below:

CREATE TABLE v_jira_custom_fields   (Project VARCHAR(100), customfield_id INT, custom_field_name VARCHAR(100), issue_id VARCHAR(100), string_value VARCHAR(255), number_value INT, text_value VARCHAR(MAX), date_value DATE, custom_value VARCHAR(MAX));  
      
INSERT INTO v_jira_custom_fields  VALUES  
('DAK',1,'Story Type','2222','4455',NULL,NULL,NULL,NULL),  
('DAK',2,'Impacted Application','2222','1213',NULL,NULL,NULL,'Diary Application'),  
('DAK',3,'Story Points','2222',NULL,6,NULL,NULL,NULL),  
('DAK',4,'Start Date','2222',NULL,NULL,NULL,'2021-01-20',NULL),  
('DAK',5,'End Date','2222',NULL,NULL,NULL,'2021-01-24',NULL),  
('DAK',6,'Ready','2222',NULL,NULL,NULL,NULL,'No')  
  
CREATE TABLE jira_data   (issue_id VARCHAR(100), Issue_Name VARCHAR(100), Issue_Type VARCHAR(100),Project  VARCHAR(100));  
      
INSERT INTO jira_data  VALUES  
('2222','Improve performance','Story','DAK')  
  
select * from  
(  
select a.issue_id,Issue_Name,Issue_Type,a.Project,custom_field_name,COALESCE(cast(number_value as char),text_value,CONVERT(nvarchar(30), date_value, 126),custom_value) Value  
from jira_data a  
left join v_jira_custom_fields b on a.issue_id=b.issue_id)s  
pivot  
(max(value)  
for   
custom_field_name in ([Story Points],[Ready],[Start Date],[End Date])  
)p  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Although you can do that, I highly suggest you use the Excel add-in for Jira instead of direct database queries. It does all that work for you already.

https://marketplace.atlassian.com/apps/1221301/jira-cloud-for-excel-official?hosting=cloud&tab=overview

Answer 3

To answer your original question:

DECLARE @jira_custom_fields TABLE (Project VARCHAR(100), customfield_id INT, custom_field_name VARCHAR(100), issue_id VARCHAR(100), string_value VARCHAR(255), number_value INT, text_value VARCHAR(MAX), date_value DATE, custom_value VARCHAR(MAX));

INSERT INTO @jira_custom_fields VALUES
('DAK',1,'Story Type','2222','4455',NULL,NULL,NULL,NULL),
('DAK',2,'Impacted Application','2222','1213',NULL,NULL,NULL,'Diary Application'),
('DAK',3,'Story Points','2222',NULL,6,NULL,NULL,NULL),
('DAK',4,'Start Date','2222',NULL,NULL,NULL,'2021-01-20',NULL),
('DAK',5,'End Date','2222',NULL,NULL,NULL,'2021-01-24',NULL),
('DAK',6,'Ready','2222',NULL,NULL,NULL,NULL,'No'),
('DAK',1,'Story Type','3333','4455',NULL,NULL,NULL,NULL),
('DAK',2,'Impacted Application','3333','123',NULL,NULL,NULL,'Diary Application'),
('DAK',3,'Story Points','3333',NULL,5,NULL,NULL,NULL),
('DAK',4,'Start Date','3333',NULL,NULL,NULL,'2021-02-20',NULL),
('DAK',5,'End Date','3333',NULL,NULL,NULL,'2021-02-24',NULL),
('DAK',6,'Ready','3333',NULL,NULL,NULL,NULL,'Yes')


SELECT *
FROM (
    SELECT Project, issue_id, custom_field_name as columnname, 
        COALESCE(string_value, CAST(number_value AS VARCHAR(10)), text_value, CONVERT(varchar(25),date_value,101), custom_value) as [value]
    FROM @jira_custom_fields
) o
pivot (
max(value)
for columnname IN ([Story Type],[Impacted Application],[Story Points],[Start Date],[End Date],[Ready])
) piv;