none
Rows to Columns RRS feed

  • Question

  • Hi all, 

    this question may have been asked many times but I cant get my data work with the sample queries I found on different sites, 

    I have a challenge which I need to pivot my table /date, 

    my data looks like below; 

         ID Bussiness                          Date                                       Value
    1          GPS Nov-18 3
    2          GPS Dec-18 2
      3          GPS Nov-18 2
      4          GPS Dec-18 3

    and my aim is to have a out put like below;

           ID        Bussiness  Nov-18 Dec-18
          1          GPS 3 2
    2        GPS 2 3

    I appreciates any help, if you let me know the syntax and way I should apply to query. 

    thanks 

    Monday, December 9, 2019 3:54 PM

All replies

  • What did you try?

    Can you post your table DDL and sample data in scripts? Thanks.

    Monday, December 9, 2019 4:19 PM
    Moderator
  • Hi thanks for your reply I have tried union, case statements but only on Access so far, I am awaiting to get sqlserver permission  
    Monday, December 9, 2019 4:31 PM
  • CREATE TABLE mytable(
       ID   INT   NOT NULL   
      ,Bussiness VARCHAR(3) NOT NULL
      ,Date VARCHAR(10) NOT NULL
      ,Value      INT  NOT NULL
      
    );
    INSERT INTO mytable( ID,Bussiness,Date, Value) VALUES
     (1,'GPS','Nov-18',3 )
    ,(2,'GPS','Dec-18',2 )
    ,(3,'GPS','Nov-18',2 )
    ,(4,'GPS','Dec-18',3 );
    
    
    Select rn,Bussiness,
    Max(Case when Date='Nov-18' then Value else null end) 'Nov-18' 
    ,Max(Case when Date='Dec-18' then Value else null end) 'Dec-18' 
    
    from  
    
    (select *, row_number() Over(partition by Date Order by ID) rn 
    from mytable) t
    Group by rn,Bussiness
    
    drop TABLE mytable


    Monday, December 9, 2019 5:09 PM
    Moderator
  • Hey, 

    Thank you so much for that, 

    I have one question though, 

    Select rn,Bussiness, Max(Case when Date='Nov-18' then Value else null end) 'Nov-18' ,Max(Case when Date='Dec-18' then Value else null end) 'Dec-18' from (select *, row_number() Over(partition by Date Order by ID) rn from mytable) t Group by rn,Bussiness drop TABLE mytable

    this query assumes we know what data we are getting but it is not always going to be that date,

    what is the option to have it dynamic, rather than filter or apply the data in the query

    regards

    Monday, December 9, 2019 5:27 PM
  • If you want a dynamic query to pivot your data, you need to understand a static solution first.

    If you need more assistance, you need to provide your sample table structure(DDL) and some sample data and your expected result. 

    You should have known how to post these from the sample I posted early.

    Monday, December 9, 2019 5:49 PM
    Moderator
  • In addition to Jingyang's post, where do you ultimately plan to consume the data?

    The reason I ask this question is that you are asking for a non-relational operation, so this is something that does not really fit with a relational DBMS like SQL Server. It can be done with dynamic SQL, but that is also an advanced solution, and not really apt for a beginner. At the same time, you may be aiming to present this in something which is a lot better fitted for the task. After all, you are asking for a presentation feature, so the presentation layer is the obvious place do to this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 9, 2019 11:04 PM
  • Hi hatra,

    Please tr following script and please try to use pivot to get your expected result .

    IF OBJECT_ID('mytable') IS NOT NULL drop table  mytable   
    go 
    CREATE TABLE mytable(
       ID   INT   NOT NULL   
      ,Bussiness VARCHAR(3) NOT NULL
      ,Date VARCHAR(10) NOT NULL
      ,Value      INT  NOT NULL
      
    );
    INSERT INTO mytable( ID,Bussiness,Date, Value) VALUES
     (1,'GPS','Nov-18',3 )
    ,(2,'GPS','Dec-18',2 )
    ,(3,'GPS','Nov-18',2 )
    ,(4,'GPS','Dec-18',3 );
    
    ------CASE WHEN Dynamic-------------
    go
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    SELECT @sql_col = ISNULL(@sql_col + '
    ,','') + 'Max(Case when Date='''+date+''' then Value else null end) '+QUOTENAME(Date) FROM mytable GROUP BY Date
    SET @sql_str = '
    Select rn,Bussiness,
    '+@sql_col+'
    from  
    (select *, row_number() Over(partition by Date Order by ID) rn 
    from mytable) t
    Group by rn,Bussiness'
    PRINT (@sql_str)
    EXEC (@sql_str)
    /*
    Select rn,Bussiness,
    Max(Case when Date='Dec-18' then Value else null end) [Dec-18]
    ,Max(Case when Date='Nov-18' then Value else null end) [Nov-18]
    from  
    (select *, row_number() Over(partition by Date Order by ID) rn 
    from mytable) t
    Group by rn,Bussiness
    rn                   Bussiness Dec-18      Nov-18
    -------------------- --------- ----------- -----------
    1                    GPS       2           3
    2                    GPS       3           2
    */
    
    ------PIVOT-------------
    go
    ;with cte as(
    select row_number()over(partition by date order by ID)ID,
    Bussiness,Date, Value 
    from mytable
    )
    SELECT *
    FROM cte
    PIVOT (
    	MAX([Value])
    	FOR Date IN ( [Nov-18],[Dec-18])
    ) AS pvt
    
    
    ------PIVOT Dynamic-------------
    go
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(Date) FROM mytable GROUP BY Date
    SET @sql_str = '
    ;with cte as(
    select row_number()over(partition by date order by ID )ID,
    Bussiness,Date, Value 
    from mytable
    )
    SELECT *
    FROM cte
    PIVOT (
    	MAX([Value])
    	FOR Date IN ('+@sql_col+')
    ) AS pvt'
    PRINT (@sql_str)
    EXEC (@sql_str)
    /*
    ;with cte as(
    select row_number()over(partition by date order by ID )ID,
    Bussiness,Date, Value 
    from mytable
    )
    SELECT *
    FROM cte
    PIVOT (
    	MAX([Value])
    	FOR Date IN ([Dec-18],[Nov-18])
    ) AS pvt
    ID                   Bussiness Dec-18      Nov-18
    -------------------- --------- ----------- -----------
    1                    GPS       2           3
    2                    GPS       3           2
    */
    
    
    

    Best Regards,

    Rachel 


    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.

    Tuesday, December 10, 2019 3:32 AM
  • Thanks Rachel, I appreciate your help and advice 
    Tuesday, December 10, 2019 9:50 AM
  • Hey Rachel I am getting this error 

    

    Tuesday, December 10, 2019 10:14 AM
  • Hey Rachel I am getting this error 

    ?<https://social.technet.microsoft.com/Forums/getfile/1513050>

    This is a forum for SQL Server. If you want help with writing queries in Access, you need to turn to a forum devoted for Access.
    If you are accessing SQL Server from Access, you will need to explain more what you and Access are doing. Not everyone here knows Access. I certainly do not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 10, 2019 10:42 PM
  • What you are doing is called a crosstab.  

    look here. https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/


    I'd rather live with false hope than with false despair.

    Wednesday, December 11, 2019 12:18 AM
  • Hi ,

    Thank you for your issue . This is  SQL Server > Transact-SQL Forum . 

    Per your error message, I think your issue might be related to Access.  If so, please post your issue in the corresponding forum and you will get more professional help  . Access Forum 

     


    By the way, if you have post your issue in the corresponding forum  ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    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.

    Wednesday, December 11, 2019 8:51 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    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, December 20, 2019 7:25 AM