Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query Pivot column help SQL 2012 Need Guru

Posted on 2013-11-21
6
Medium Priority
?
305 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 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