Solved

SQL Query Pivot column help SQL 2012 Need Guru

Posted on 2013-11-21
6
294 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
6 Comments
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 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 48

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 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:ScottPletcher
ScottPletcher earned 100 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now