locked
Get Row Data as Columns using Pivot RRS feed

  • 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

    Tuesday, September 11, 2018 6:46 PM

All replies

  • 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

    Tuesday, September 11, 2018 7:16 PM
  • 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.


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

    Tuesday, September 11, 2018 11:39 PM
  • 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 MSDNFSF@microsoft.com.

    • Proposed as answer by pituachMVP Wednesday, September 12, 2018 1:30 PM
    Wednesday, September 12, 2018 2:11 AM
  • 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


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

    Wednesday, September 12, 2018 1:30 PM
  • it’s returning the same financialvalue for every period. something is wrong with query.
    Wednesday, September 12, 2018 4:39 PM
  • Here is the current result 


    • Edited by zaveri cc Wednesday, September 12, 2018 6:53 PM
    Wednesday, September 12, 2018 6:47 PM
  • i need result like below
    Wednesday, September 12, 2018 6:54 PM
  • 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


    Wednesday, September 12, 2018 7:19 PM
  • 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

    Wednesday, September 12, 2018 7:22 PM
  • 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

    Wednesday, September 12, 2018 7:35 PM
  • Don't worry about fility type column
    Wednesday, September 12, 2018 8:25 PM
  • Why you added Min function in the query?
    Wednesday, September 12, 2018 8:38 PM
  • 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.


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

    Wednesday, September 12, 2018 9:41 PM
  • 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 MSDNFSF@microsoft.com.

    Thursday, September 13, 2018 7:23 AM
  • Your query gives same financialValue for each quarter which is wrong.
    Thursday, September 13, 2018 12:45 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, September 14, 2018 10:19 AM
  • 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

    Friday, September 14, 2018 10:45 AM
  • 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

    Friday, September 14, 2018 10:47 AM