Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Pivot column help SQL 2012 Need Guru

Posted on 2013-11-21
6
Medium Priority
?
302 Views
Last Modified: 2013-11-25
Hello all,

Okay hoping to find an example on how to accomplish this.   So I have a table called YearlyAnalysis that has the following columns:

CustomerID
Year
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

Sample there are other columns this is good enough.   So what I need to do is take the current Year (getdate()) and go back 3 years.   What I am trying to get is columns such as this let's say we are in 2013 right so this:

                                  2010       2011      2012     2013
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

If the year does not exist I still want a column for it with all nulls.  Can anyone provide me a sample way to do this?   I am using SQL Server 2012.
0
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 400 total points
ID: 39667431
Try this:
select CustomerID, 'Revenue' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, Revenue from pivot_example ) t
pivot (sum(Revenue)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'TotalAmount' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, TotalAmount from pivot_example ) t
pivot (sum(TotalAmount)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'LeaseAmount' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, LeaseAmount from pivot_example ) t
pivot (sum(LeaseAmount)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'CurrentAssets' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, CurrentAssets from pivot_example ) t
pivot (sum(CurrentAssets)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'CurrentLiabilities' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, CurrentLiabilities from pivot_example ) t
pivot (sum(CurrentLiabilities)  for Year in ([2010],[2011],[2012],[2013])) as pvt

Open in new window

0
 

Author Comment

by:sbornstein2
ID: 39667842
I think this might work I am having one problem so what I am trying to do is instead of hardcoding the years I am doing this and year is an int field I have

select CustomerID, 'Revenue' as Metric, YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE())  from
(SELECT CustomerID, Year, Revenue from pivot_example ) t
pivot (sum(Revenue)  for Year in (YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE()))) as pvt

It does not like the GETDATE at all.  Is there a way to do this?  This way my query will never need changing for the years.

Thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39667883
There's some bad news I'm afraid.

It is not possible to give those year columns names that change unless you use "dynamic sql". If you are prepared to have columns names like this:

Year -3   Year-2   Year-1   Current Year

Then you don't need "dynamic sql".

Do you absolutely need those column names to change?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39667928
Here is an approach without needing unions (& without dynamic column labels):
    CREATE TABLE YearlyAnalysis 
    ([CustomerID] int, [Year] int, [Revenue] int, [TotalAmount] int, [LeaseAmount] int, [CurrentAssets] int, [CurrentLiabilities] int)
;
    
INSERT INTO YearlyAnalysis 
    ([CustomerID], [Year], [Revenue], [TotalAmount], [LeaseAmount], [CurrentAssets], [CurrentLiabilities])
VALUES
    (1, 2013, 101, 102, 103, 104, 105),
    (1, 2012, 101, 102, 103, 104, 105),
    (1, 2011, 101, 102, 103, 104, 105),
    (1, 2010, 101, 102, 103, 104, 105),
    (1, 2009, 101, 102, 103, 104, 105)
;

**Query 1**:

SELECT
        label
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 3 THEN Value ELSE 0 END ) AS "Year - 3"
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 2 THEN Value ELSE 0 END ) AS "Year - 2"
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 1 THEN Value ELSE 0 END ) AS "Year - 1"
      , SUM ( CASE WHEN [year] =   datepart(YEAR,getdate())       THEN Value ELSE 0 END ) AS "Curent Year"
FROM (
      SELECT
              agr.[Year]
            , ca1.label
            , ca1.value
      FROM (
            SELECT
                    [Year]
                  , SUM ( Revenue ) AS Revenue
                  , SUM ( TotalAmount ) AS  TotalAmount
                  , SUM ( LeaseAmount ) AS  LeaseAmount
                  , SUM ( CurrentAssets ) AS  CurrentAssets
                  , SUM ( CurrentLiabilities ) AS  CurrentLiabilities
            FROM YearlyAnalysis
            WHERE [year] > = ( datepart(YEAR,getdate()) ) - 3
            GROUP BY [Year]
           ) AS agr
      CROSS APPLY (
                   VALUES
                        ('Revenue'           , agr.revenue)
                      , ('TotalAmount'       , agr.TotalAmount)
                      , ('LeaseAmount'       , agr.LeaseAmount)
                      , ('CurrentAssets'     , agr.CurrentAssets)
                      , ('CurrentLiabilities', agr.CurrentLiabilities)
                  ) AS ca1 (label, value)
     ) AS d
GROUP BY label
ORDER BY CASE
          WHEN label = 'Revenue' THEN 1
          WHEN label = 'TotalAmount' THEN 2
          WHEN label = 'LeaseAmount' THEN 3
          WHEN label = 'CurrentAssets' THEN 4
          WHEN label = 'CurrentLiabilities' THEN 5
          ELSE 99
         END
    
    


**[Results][2]**:

|              LABEL | YEAR - 3 | YEAR - 2 | YEAR - 1 | CURENT YEAR |
|--------------------|----------|----------|----------|-------------|
|            Revenue |      101 |      101 |      101 |         101 |
|        TotalAmount |      102 |      102 |      102 |         102 |
|        LeaseAmount |      103 |      103 |      103 |         103 |
|      CurrentAssets |      104 |      104 |      104 |         104 |
| CurrentLiabilities |      105 |      105 |      105 |         105 |



[1]: http://sqlfiddle.com/#!3/2ead5/15

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 39667998
If you're willing to save the results into an intermediate table first, it's easy enough to rename the columns while still avoiding dynamic SQL.  Technically the order of the rows coming out of the last SELECT is not guaranteed, but since it's SELECTing from a heap and there are an extremely small number of rows, it's pretty much a lock that they will come out in the same, sorted order in which they were INSERTed.


USE master

IF OBJECT_ID('tempdb.dbo.#YearlyAnalysis') IS NOT NULL
    DROP TABLE #YearlyAnalysis
IF OBJECT_ID('tempdb.dbo.#YearlyAnalysis_Report') IS NOT NULL
    DROP TABLE #YearlyAnalysis_Report

CREATE TABLE #YearlyAnalysis (
    [CustomerID] int, [Year] smallint,
    [Revenue] int, [TotalAmount] int, [LeaseAmount] int, [CurrentAssets] int, [CurrentLiabilities] int
)

--TRUNCATE TABLE #YearlyAnalysis
INSERT INTO #YearlyAnalysis
    ( [CustomerID], [Year], [Revenue], [TotalAmount], [LeaseAmount], [CurrentAssets], [CurrentLiabilities] )
VALUES
    (1, 2013, 00064, 00128, 00256, 00512, 01024),
    (1, 2013, 00032, 00064, 00128, 00256, 00512),
    (1, 2012, 00016, 00032, 00064, 00128, 00256),
    (1, 2011, 00008, 00016, 00032, 00064, 00128),
    (1, 2010, 00004, 00008, 00016, 00032, 00064),
    (1, 2009, 00002, 00004, 00008, 00016, 00032),
    (1, 2008, 00001, 00002, 00004, 00008, 00016)
;

DECLARE @CustomerID int
DECLARE @EndingYear smallint

SET @CustomerID = 1
SET @EndingYear = YEAR(GETDATE())

-------------------------------------------------------------------------------

DECLARE @NewColumnName sysname

SELECT
    ColumnName,
    --optionally add other older year(s) here
    SUM(CASE WHEN Year = @EndingYear - 3 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 3],
    SUM(CASE WHEN Year = @EndingYear - 2 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 2],
    SUM(CASE WHEN Year = @EndingYear - 1 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 1],
    SUM(CASE WHEN Year = @EndingYear     THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year]
INTO #YearlyAnalysis_Report
FROM #YearlyAnalysis
CROSS JOIN (
    SELECT 1 AS ColumnNumber, 'Revenue' AS ColumnName UNION ALL
    SELECT 2, 'TotalAmount' UNION ALL
    SELECT 3, 'LeaseAmount' UNION ALL
    SELECT 4, 'CurrentAssets' UNION ALL
    SELECT 5, 'CurrentLiabilities'
    --optionally add additional table columns here
) AS Columns
GROUP BY
    ColumnNumber,
    ColumnName
ORDER BY
    ColumnNumber

SET @NewColumnName = CAST(@EndingYear - 3 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 3]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear - 2 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 2]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear - 1 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 1]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year]', @NewColumnName, 'COLUMN'

SELECT *
FROM #YearlyAnalysis_Report
0
 

Author Closing Comment

by:sbornstein2
ID: 39675892
these were all helpful actually thanks all
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question