Get Row Data as Columns using Pivot

Question

Hi All,

i'm using below query which works well but i need rows ('AAA123','BBB176','CCC175') as columns

SELECT  DISTINCT  convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2)AS QuarterPeriod,unv.CallReportPOR_ext.FDICCertificateNumber, 
unv.CallReportPOR_ext.FinancialInstitutionName, unv.CallReportPOR_ext.FinancialInstitutionState,unv.CallReportPOR_ext.FinancialInstitutionFilingType , 
Data.FinancialHistory.FinancialValue, Data.DataPoint.DataPointLabel FROM unv.CallReportPOR_ext INNER JOIN 
unv.EntityIdentifier_ext ON unv.CallReportPOR_ext.FDICCertificateNumber = unv.EntityIdentifier_ext.Value INNER JOIN 
Data.FinancialHistory ON unv.EntityIdentifier_ext.EntityId = Data.FinancialHistory.EntityId INNER JOIN 
Data.DataPoint ON Data.FinancialHistory.DataPointId = Data.DataPoint.DataPointId 
where Entityidentifiertypeid = 1 and FinancialInstitutionName='WELLS FARGO CENTRAL BANK' and DataPointLabel in ('AAA123','BBB176','CCC175')
order by QuarterPeriod DESC

Thanks,

Zav

Answer 1

Please post how current result is and what you expect to see

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries  to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 3

Hi Zav,

I guess this is what you want.

;WITH CTE AS
(
SELECT  
DISTINCT  
convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2)AS QuarterPeriod,
unv.CallReportPOR_ext.FDICCertificateNumber, 
unv.CallReportPOR_ext.FinancialInstitutionName, 
unv.CallReportPOR_ext.FinancialInstitutionState,
unv.CallReportPOR_ext.FinancialInstitutionFilingType, 
Data.FinancialHistory.FinancialValue, 
Data.DataPoint.DataPointLabel 
FROM unv.CallReportPOR_ext 
INNER JOIN unv.EntityIdentifier_ext ON unv.CallReportPOR_ext.FDICCertificateNumber = unv.EntityIdentifier_ext.Value 
INNER JOIN Data.FinancialHistory ON unv.EntityIdentifier_ext.EntityId = Data.FinancialHistory.EntityId 
INNER JOIN Data.DataPoint ON Data.FinancialHistory.DataPointId = Data.DataPoint.DataPointId 
where Entityidentifiertypeid = 1 
and FinancialInstitutionName='WELLS FARGO CENTRAL BANK' 
and DataPointLabel in ('AAA123','BBB176','CCC175')
--order by QuarterPeriod DESC
)
SELECT 
QuarterPeriod,FDICCertificateNumber,FinancialInstitutionName,FinancialInstitutionState,FinancialInstitutionFilingType,[AAA123],[BBB176],[CCC175]
FROM (
SELECT  QuarterPeriod,FDICCertificateNumber,FinancialInstitutionName,FinancialInstitutionState,FinancialInstitutionFilingType,FinancialValue,DataPointLabel FROM CTE
) SRC
PIVOT
(
 MAX(FinancialValue) FOR DataPointLabel IN ([AAA123],[BBB176],[CCC175])
) PVT
order by QuarterPeriod DESC

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Since we don't have any more information I will propose Will_Kong message as answer.

But I think the OP should provide more meaningful information which will let us reproduce the issue and provide answer without guessing

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 5

it’s returning the same financialvalue for every period. something is wrong with query.

Answer 6

Here is the current result 

Answer 7

i need result like below

Answer 8

From your result, the value of FinancialInstitutionFilingType is the average value (41+31+51)/3 = 41? You need to tell the logic to get your final output.

---

A Fan of SSIS, SSRS and SSAS

Answer 9

You need to learn how to post T-SQL question. Use script to present your question. Not image.

Here is a try:

CREATE TABLE mytable(
   QuarterPeriod                  VARCHAR(4) NOT NULL 
  ,FinancialInstitutionName       VARCHAR(50) NOT NULL
  ,FinancialInstitutionFilingType int NOT NULL
  ,FinancialValue                 VARCHAR(7) NOT NULL
  ,DataPointLabel                 VARCHAR(40) NOT NULL
 
);
INSERT INTO mytable(QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,FinancialValue,DataPointLabel ) VALUES
 ('4Q17','WELLS FARGO CENTRAL BANK',41,'30854','AAA123')
,('3Q17','WELLS FARGO CENTRAL BANK',41,'30923','AAA123')
,('4Q17','WELLS FARGO CENTRAL BANK',31,'39833','BBB176' )
,('3Q17','WELLS FARGO CENTRAL BANK',31,'40651','BBB176')
,('4Q17','WELLS FARGO CENTRAL BANK',51,'43280', 'CCC175' )
,('3Q17','WELLS FARGO CENTRAL BANK',51,'46240','CCC175');


select QuarterPeriod
,FinancialInstitutionName
--,FinancialInstitutionFilingType
,Max(Case when DataPointLabel='AAA123' then FinancialValue Else null End) [AAA123]
,Max(Case when DataPointLabel='BBB176' then FinancialValue Else null End) [BBB176]
,Max(Case when DataPointLabel='CCC175' then FinancialValue Else null End) [CCC175]
  from mytable
where QuarterPeriod='4Q17'
Group by  QuarterPeriod
,FinancialInstitutionName
--,FinancialInstitutionFilingType 


drop table mytable

Answer 10

i need result like below

Try like this

SELECT *
FROM
(
SELECT  convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2)AS QuarterPeriod,
unv.CallReportPOR_ext.FinancialInstitutionName,
unv.CallReportPOR_ext.FinancialInstitutionFilingType , 
MIN(Data.FinancialHistory.FinancialValue) AS FinancialValue, Data.DataPoint.DataPointLabel 
FROM unv.CallReportPOR_ext INNER JOIN 
unv.EntityIdentifier_ext ON unv.CallReportPOR_ext.FDICCertificateNumber = unv.EntityIdentifier_ext.Value INNER JOIN 
Data.FinancialHistory ON unv.EntityIdentifier_ext.EntityId = Data.FinancialHistory.EntityId INNER JOIN 
Data.DataPoint ON Data.FinancialHistory.DataPointId = Data.DataPoint.DataPointId 
where Entityidentifiertypeid = 1 and FinancialInstitutionName='WELLS FARGO CENTRAL BANK' and DataPointLabel in ('AAA123','BBB176','CCC175')
GROUP BY convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2),
unv.CallReportPOR_ext.FinancialInstitutionName,
unv.CallReportPOR_ext.FinancialInstitutionFilingType ,  Data.DataPoint.DataPointLabel 
)t
PIVOT(MIN(FinancialValue)
FOR DataPointLabel  IN ([AAA123],[BBB176],[CCC175]))p
order by QuarterPeriod DESC

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 11

Don't worry about fility type column

Answer 12

Why you added Min function in the query?

Answer 13

it’s returning the same financialvalue for every period. something is wrong with query.

Then please provide us the information we asked for instead of images and stories :-)

If this is still open issue then please post:

1) Queries to CREATE your table(s) including indexes
2) Queries  to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 14

i need result like below

Here is the current result 

Hi Zaveri cc,

Per the two screenshots above, the query statement could be written like this.

;WITH CTE AS
(
SELECT  
DISTINCT  
convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2)AS QuarterPeriod,
--unv.CallReportPOR_ext.FDICCertificateNumber, 
unv.CallReportPOR_ext.FinancialInstitutionName, 
--unv.CallReportPOR_ext.FinancialInstitutionState,
unv.CallReportPOR_ext.FinancialInstitutionFilingType, 
Data.FinancialHistory.FinancialValue, 
Data.DataPoint.DataPointLabel 
FROM unv.CallReportPOR_ext 
INNER JOIN unv.EntityIdentifier_ext ON unv.CallReportPOR_ext.FDICCertificateNumber = unv.EntityIdentifier_ext.Value 
INNER JOIN Data.FinancialHistory ON unv.EntityIdentifier_ext.EntityId = Data.FinancialHistory.EntityId 
INNER JOIN Data.DataPoint ON Data.FinancialHistory.DataPointId = Data.DataPoint.DataPointId 
where Entityidentifiertypeid = 1 
and FinancialInstitutionName='WELLS FARGO CENTRAL BANK' 
and DataPointLabel in ('AAA123','BBB176','CCC175')
--order by QuarterPeriod DESC
)
SELECT 
QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,[AAA123],[BBB176],[CCC175]
FROM (
SELECT  QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,FinancialValue,DataPointLabel FROM CTE
) SRC
PIVOT
(
 MIN(FinancialValue) FOR DataPointLabel IN ([AAA123],[BBB176],[CCC175])  --Get the minimum value of the column "FinancialValue" based on the group columns(QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,DataPointLabel)
) PVT
order by QuarterPeriod DESC

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 15

Your query gives same financialValue for each quarter which is wrong.

Answer 16

Your query gives same financialValue for each quarter which is wrong.

Oh, I see.

You mean that you just want to convert the values of the column "DataPointLabel" to columns, not get minimum values or maximum values for each group (QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,FinancialValue), for this purpose, you need to add row number column to the select list, then use pivot operator to convert the row values to columns.

;WITH CTE AS
(
SELECT  
DISTINCT  
convert(varchar(1),DATEPART(q,PERIOD)) + 'Q' + right(year(PERIOD),2)AS QuarterPeriod,
--unv.CallReportPOR_ext.FDICCertificateNumber, 
unv.CallReportPOR_ext.FinancialInstitutionName, 
--unv.CallReportPOR_ext.FinancialInstitutionState,
unv.CallReportPOR_ext.FinancialInstitutionFilingType, 
Data.FinancialHistory.FinancialValue, 
Data.DataPoint.DataPointLabel 
FROM unv.CallReportPOR_ext 
INNER JOIN unv.EntityIdentifier_ext ON unv.CallReportPOR_ext.FDICCertificateNumber = unv.EntityIdentifier_ext.Value 
INNER JOIN Data.FinancialHistory ON unv.EntityIdentifier_ext.EntityId = Data.FinancialHistory.EntityId 
INNER JOIN Data.DataPoint ON Data.FinancialHistory.DataPointId = Data.DataPoint.DataPointId 
where Entityidentifiertypeid = 1 
and FinancialInstitutionName='WELLS FARGO CENTRAL BANK' 
and DataPointLabel in ('AAA123','BBB176','CCC175')
--order by QuarterPeriod DESC
),
CTE_ROW AS 
(
SELECT 
QuarterPeriod,
FinancialInstitutionName,
FinancialInstitutionFilingType,
FinancialValue,
DataPointLabel,
ROW_NUMBER() OVER (ORDER BY QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,DataPointLabel,FinancialValue) AS RN
FROM CTE
)
SELECT 
QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,[AAA123],[BBB176],[CCC175]
FROM (
SELECT  RN,QuarterPeriod,FinancialInstitutionName,FinancialInstitutionFilingType,FinancialValue,DataPointLabel FROM CTE_ROW
) SRC
PIVOT
(
 MAX(FinancialValue) FOR DataPointLabel IN ([AAA123],[BBB176],[CCC175])  
) PVT
order by QuarterPeriod DESC

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 17

Why you added Min function in the query?

Because your data showed two rows for each DataPointLabel value and your output showed minimum FinancialValue out for them

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 18

Your query gives same financialValue for each quarter which is wrong.

it wont if your data is exactly like what you showed us. If you have additional columns then it may not work. We can only work with the data you gave us cannot guess on what else can come in. So make sure you give us proper data if its different from what you posted before

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page