?
Solved

SQL Query Pivot column help SQL 2012 Need Guru

Posted on 2013-11-21
6
Medium Priority
?
301 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 69

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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